Export multiple BLOB's from Oracle using SQL Developer

 My customer came to me with a question. He wanted to export some 150 PDF documents out of the Oracle database. The documents were stored in a BLOB column in a table, together with the filename. The only solution he had was to export each file manually by selecting the row and download the file, giving it the right filename. 

Now at first I did not have another solution. I thought about writing a procedure to bundle the files using APEX_ZIP, storing that in a table and the downloading this file. But we had no access to a table in which this file could be stored. 

Then while searching the internet I came across an solution from Jeff Smith (yes, that Jeff Smith). When exporting a table/query result you can choose Loader as export type. For this type the BLOB columns are exported to separate files (with technical names) and an accompanying CLT file to load the rest of the data. This way the files can be downloaded but they don't yet have the right file name. 

This can be tackled by using the following query with the italic text replaced with appropriate table and column names:

select blob_content

     , file_name

  from table_with_blob_content

Use the following parameters to export the resulting rows of the query:

The content of the resulting TABLE_EXPORT_DATA.ldr file looks like this:

filename1|TABLE_EXPORT_DATA_3c11e4a1a-0191-1000-8011-0a57605edcf0.ldr|{EOL} filename2| etc...

Do some smart editing on this file to generate the following result (for Windows). I used Notepad++ because it can insert returns in a replace action:

ren filename1 TABLE_EXPORT_DATA_3c11e4a1a-0191-1000-8011-0a57605edcf0.ldr

ren filename2 etc...

When this file is executed on Windows all the .ldr files will be given the right file names. 

Within a few minutes all 150 files were exported and renamed. 

My customer was very happy, preventing him from having a mouse arm ;-). 

Happy developing