Monday, 10 September 2012

Extracting Files from LOB column in Oracle Database - How to Export LOBs to Filesystem

I recently needed to extract data that had been stored as a CLOB (LOB) column in an Oracle database table. The data was a series of PDF and DOC files that had been saved as LOBs but I needed to save the files to a filesystem for use on a website rather than being within the Oracle database.

There were a number of scripts that I found that could save LOB data from Oracle database tables but they were very clunky and manual that may have been suitable for extracting and saving a file from a single CLOB field in Oracle but were totally inappropriate for saving multiple files from a LOB column on a table.

Eventually after many Google searches for various terms I found a piece of software called OraLOB Editor that quickly and easily allowed the export of the files stored as LOBs to the file system. It almost instantly processed every row of the relevant table and created separate files for each PDF stored in the CLOB field.

OraLOB Editor also allows the import of LOB data and appears to be the only tool that has this level of versatility for extracting files from LOB data in Oracle with any level of automation for saving multiple files to the filesystem.

OraLOB Editor has a free 30 day trial so that you can test whether it works sufficiently for your needs and only costs $75 for the full licence which is a bargain compared to the effort that would be required to script the process and then manually run it.

No comments:

Post a Comment