Oracle: Exporting/Importing tables containing CLOB data

Exporting the table using SQLWorkbench

Imagine you have a table images with a character column filename and a clob column image_data

First you need to install the SQL-Workbench. Install does just mean to extract the archive in that case.

The following command exports the table into a textfile. It does also generate the loader file which can be used with sqlldr. The CLOB data is exported as files with the name image_data_0, image_data_1, ... and will be in the same directory. Those files are referenced in the textfile.

WbExport -type=text -file='c:/temp/image_data.txt' -delimiter='|' -decimal=',' -sourcetable=image_data -formatfile=oracle;

Importing the table using sqlldr

To import the table you need to have the generated control file (image_data.ctl), the textfile (image_data.txt) and all the clob data files (image_data_*).

The command is easy:

sqlldr user@OURORA/password control=image_data.ctl

The above command assumes that we have a user named user which has the password password. And we assume that there is the server address OURORA configured in the tnsnames.ora

Possible errors

Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not fou
nd; No message file for product=RDBMS, facility=UL

If you get that error delete the ORACLE_HOME environment variable then it should work...