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