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