loading data to a table containing clob datatype, from a flat file

Question

Hi guys, I am trying to populate a table with the values taken from a flat file using SQLLDR.The problem is that one of the fields in the table is of CLOB datatype(lets say the field name is COMMENTS). In the control file, i have mentioned the datatype for this field as CHAR(4000), since the usage of CLOB in control file was not working. But this also fails when there is a EndOfLine present in the field. Thus it tries to load the next line as new record and fails.

Please suggest a solution to this.... Thanks in advance

Answer 1

You may have to do this programatically using perl or something similar. Alternatively, you can use something like the example given here:

http://www.oracle-base.com/articles/10g/LoadLobDataUsingSqlLoader.php

Answer 2

You don't say why the CLOB specification in the control file failed, but if you're at 9i or later, you should be able to load a CLOB via sqlldr.

Failing that, you might be able to work out assembling logical records from physical records via CONTINUEIF.