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:
- Viewing BLOBs as pictures
- Viewing BLOBs in External Editors (PDFs, spreadsheets, etc.)
- Including BLOBs in your PDF reports
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
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 after this finishes, you’ll have something like this:
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.