Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sqlldr with CLOBs in single file problem

970752Nov 9 2012 — edited Oct 21 2013

I am stuck trying to resolve this problem. I am migrating data from DB2 to Oracle. I used DB2 export to extract the data specifying lobsinfile clause. This created all the CLOB data in one file. So a typical record has a column with a reference to the CLOB data. "OUTFILE.001.lob.0.2880/". where OUTFILE.001.lob is the name specified in the export command and 0 is the starting position in the file and 2880 is the length of the first CLOB.

When I try to load this data using sqlldr I'm getting a file not found.

The control file looks something like this:
....
...
clob_1 FILLER char(100),
"DETAILS" LOBFILE(clob_1) TERMINATED BY EOF,
...
...

I'm using Oracle 11gR2 and DB2 9.7.5

Your help is appreciated.

More Details

Primary data file is VOIPCACHE.dat Secondary datafile (file with lob data) is VOIPCACHE.001.lob

Control File

load data
infile 'VOIPCACHE.dat'
badfile 'VOIPCACHE.bad'
discardfile 'VOIPCACHE.dsc'
replace into table VOIPCACHE
fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS
(KEY1 "rtrim(:KEY1)",
.....
.....
FIELD8,
clob_1 FILLER char (100),
"DATA" LOBFILE(clob_1) TERMINATED BY EOF)

Snippet from Log file

IELD7 NEXT * , O(") CHARACTER
FIELD8 NEXT * , O(") CHARACTER
CLOB_1 NEXT 100 , O(") CHARACTER
(FILLER FIELD)
"DATA" DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field CLOB_1

SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.0.0/' for field "DATA" table VOIPCACHE
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.0.47/' for field "DATA" table VOIPCACHE
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.47.47/' for field "DATA" table VOIPCACHE
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.94.58/' for field "DATA" table VOIPCACHE
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.152.58/' for field "DATA" table VOIPCACHE
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-502: unable to open data file 'VOIPCACHE.001.lob.210.206/' for field "DATA" table VOIPCACHE
......
......
This is repeated for each record

sqlldr command

sqlldr userid=${SCHEMA}/${PASSWD}@$ORACLE_SID control=${CTLDIR}/${tbl}.ctl log=${LOGDIR}/${tbl}.log direct=true errors=50
I dont think the variables are important here

-EC