Exporting Multiple BLOBs with Oracle SQL Developer

thatjeffsmith SQL Developer 22 Comments

Tell Others About This Story:

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 22

    1. thatjeffsmith Post
      Author
  1. Hi Jeff,

    it seems that this is also exporting virtual columns. Is there a way to prevent this in 4.1.5?

    Thank you & Best regards
    Salek

    1. thatjeffsmith Post
      Author
  2. I have exported the contents to deploy.zip . How do I import the data using the exported file. Do u have any document for that.

    1. thatjeffsmith Post
      Author
      1. Yes. Without the compress option , I see multiple files with control file. Will use the same control file and will use sql loader to load it. Now it is clear . Thanks for the prompt response.

  3. thanks… useful info… i was able to export using cart and import blob data with above steps using sqlldr… only thing i did not do is check compressed box as whenever i checked compressed, blob data file did not generate in the zip file. So i left compressed option unchecked & blob data file generated.

    SQL developer version: Version 4.0.2.15 Build 15.21

  4. Hi Jeff,

    I have something I’ve been struggling with for a bit of time. I have a very large table (172GB ) in Oracle (version 11.2) that I need to export and load to another database. I’ve been using the export utility of SQL Developer (version 4.1.1) with Loader as the format. Next I ran a compress and got the data down to 152Gb. The utility runs fine, however, since the export is so large, I would like to be able to export into multiple directories. This way I can zip each directory and upload to my AWS S3 bucket in say 5 or 10GB chunks.

    I’m hoping to be able to continue using SQL Developer and loader as the format as my scripts are running perfectly with .ldr and I’m unloading BLOB data.

    Is there a way to perform this?

    Much appreciation in advance,
    Jennifer

  5. HOW EXPORT AND IMPORT BLOB COLUMN DATA OVER TWO TABLES AMONG TWO DIFFERENT SERVERS BY USING ORACLE SQL DEVELOPER ..??? IS IT POSSIBLE?? AND HOW??

  6. It would be great to be able to export the blobs with useful names (using the keys value from the table) like Toad does. If this was possible we could finally use Toad for extracting all data and giving to customers as needed.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
      1. Thanks for the fast reply and fixing the link! I have another question. I’m using sql developer across a vpn, to connect to my 10g aix server (exporting) and my 11g rhel server (importing). So I am assuming I need to have sql developer run locally in an x-session so that I can get my deploy file containing the lobs created locally on the AIX server, ftp over to the RHEL then run sqlloader plus the .sql generated in the deploy.zip to load the blobs. Is that right?
        Thanks Cathy

        1. thatjeffsmith Post
          Author
  7. Hi Jeff,

    I recently found out about this possibilty by playing around with the export settings.
    But: Do I get it right that the exported BLOBs always get a generated name?

    In our tables we usually store the original file names. As these are exported as well in the loader file I can quite easily build a Windows PowerShell script to rename all the files after the export, but I wondered whether there is a better way of achieving this solely with SQL Developer.

    (For those who want to know the PowerShell command:
    Rename-Item “.ldr” “”)

    1. thatjeffsmith Post
      Author
  8. I tried the above but I didn’t get a separate file for each blob – just one file for each row. Based on your description I was hoping to get one file per row plus an extra file per blob in each row. Did I misunderstand what this is supposed to provide?

Leave a Reply

Your email address will not be published. Required fields are marked *