We're trying to load a file that contains about 40,000 records. Each record is basically one long CLOB value, up for 100,000 characters. I'm trying to use SQL Loader like this:
LOAD DATA
INFILE '/u01/TSDEV/data/data.txt'
TRUNCATE INTO TABLE commentary
(clob_filename FILLER CHAR(100),
value LOBFILE(clob_filename) TERMINATED BY EOF)
But, I am getting this on every record:
Path used: Conventional
SQL*Loader-502: unable to open data file
'[{"cid":"1","ts_id":"1","group_id":"0","c_subject":"Above is the summary of new trades across our Tr' for field VALUE table COMMENTARY
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file
'[{"cid":"2","ts_id":"1","group_id":"0","c_subject":"Is Italy the Next Greece?","c_message":"<p><span' for field VALUE table COMMENTARY
So, it sees the file, because it displays the content. But why the error? None of the data will load.
Any help is appreciated.
If the data is just inline, then you do not use LOBFILE. LOBFILE is for when the data contains the name of the file which contains the LOB data, ie, one file per line in the SQL Loader datafile. For inline data, you nominate a large CHAR size, eg
LOAD DATA
INFILE 'sample.dat'
INTO TABLE my_table
FIELDS TERMINATED BY ','
(name CHAR(25),
mylobdata CHAR(10000))
If your LOB data contains all sorts of data (newlines, delimiters etc), then you would need to prefix/suffix the data with some known value and then let SQL Loader know about it, eg
LOAD DATA
INFILE 'sample.dat'
INTO TABLE my_table
FIELDS TERMINATED BY ','
(name CHAR(25),
mylobdata CHAR(10000) ENCLOSED BY '<startlob>' AND '<endlob>')