This discussion is archived
3 Replies Latest reply: Nov 8, 2012 5:30 AM by 970752 RSS

Using sqlldr to load CLOB data from DB2

970752 Newbie
Currently Being Moderated
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.
  • 1. Re: Using sqlldr to load CLOB data from DB2
    damorgan Oracle ACE Director
    Currently Being Moderated
    File not found is a permissions issue but we've another problem with your post ... that is the actual error stack not your impression of it. Please post the full and complete stack. Thanks.
  • 2. Re: Using sqlldr to load CLOB data from DB2
    970752 Newbie
    Currently Being Moderated
    OK..here are additional details. Some names have changed but the idea is the same. Also the sqlldr is executing in the same directory as the data files and the control file

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

    Control Fileload 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
  • 3. Re: Using sqlldr to load CLOB data from DB2
    970752 Newbie
    Currently Being Moderated
    Any help is appreciated.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points