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.


I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.


  1. Thanks. This is the only solution that is portable in terms of extraction and distribution . It gives the flexibility of delivering components to be deployed in higher environments.

    • thatjeffsmith

      You could always write a sqlcl script, we have examples on GitHub, ain’t nothing wrong with that I hope

    • Brandon Paul

      Can you provide one of your example SqlCl scripts to handle renaming the output files?

  2. Salek Talangi Reply

    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 Talangi

      Ok, I really could have thought of the VIEW-solution myself.

      Thank you,

  3. Radhakrishnan.M Reply

    I have exported the contents to . How do I import the data using the exported file. Do u have any document for that.

    • Radhakrishnan Muthusundaram

      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.

  4. 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 Build 15.21

  5. 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,


  7. 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.

    • thatjeffsmith

      So ALL of the Oracle Docs links out there on blogs, no longer work as our doc site changed. Oh well.

      Try this one.

    • 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 to load the blobs. Is that right?
      Thanks Cathy

    • thatjeffsmith

      SQL*Loader is client-server – you can serve up the data from anywhere to any database. It’ll just be a LOT faster if you stage it up on the local box.

  8. 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” “”)

  9. 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?

Write A Comment