ThatJeffSmith

Exporting Multiple BLOBs with Oracle SQL Developer

BLOBs are cool. Optimized such that you can store files basically in the database, with the power of SQL AND no performance hit. That sounds like a pretty good deal.

I get asked questions a lot about accessing BLOBs once they’re in the database.

And that’s led to quite a few posts here on the subject:

But today’s question comes from Amy:

Can I export multiple blobs using SQL Developer?

So, short answer is: YES.

The little bit longer answer is: Yes, but be sure to use the SQL*Loader format preference.

And the longest answer is…

Getting your BLOBs out

There are a few ways to make this happen, but they all revolve around using the ‘Loader’ formatter available in the ‘Export’ engine that SQL Developer uses to export data.

I could right-click on a table, I could use the Tools > Database Export, or I could use the Cart.

I don’t get to talk about the Cart enough, so I’m going to show you how to use the Cart, again.

Open the Cart
This is easy, just access it from the View menu.

Then drag your objects containing the BLOBs to the Cart

After you drag the tables over, you'll want to enable the 'Data' option, and then optionally use a WHERE clause to filter out the rows you don't want included.

After you drag the tables over, you’ll want to enable the ‘Data’ option, and then optionally use a WHERE clause to filter out the rows you don’t want included.

Click the ‘Export Cart’ button
I recommend these specific options:

  • Loader – that’s for SQL*Loader
  • Separate Files – write out a file for each table (and for the blobs, a file for each of those too)
  • Compressed – zip that stuff up
So basically just like this

So basically just like this

So after this finishes, you’ll have something like this:

Note the highlighted files - those are the blobs

Note the highlighted files – those are the blobs

There’s a ‘export.sql’ script. That will go and create all of your tables for you – assuming you checked the option to export the DDL as well. And then you would need to launch SQL*Loader to load up the tables as desired.

And if you’re curious about how this mechanism actually worksthis works, check out the DOCS.