This discussion is archived
5 Replies Latest reply: Oct 21, 2013 8:24 AM by Jorden M RSS

sqlldr with CLOBs in single file problem

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.

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
  • 1. Re: sqlldr with CLOBs in single file problem
    L-MachineGun Pro
    Currently Being Moderated
    In order to load these lobs, you may need to create a single file for each one.

    You have two alternatives:
    1.-
    - Load a temporary table without the lobs but including the name column.

    - Depending on the size of the file containing all the lob's:

    a) Load the file as a huge lob and code pl/sql procedure to extract the lob data from that lob and populate the real table -- or --
    b) Code a pl/sql program that uses utl_file package to read the data from the main file and based on the start+size from the name column, populate the corresponding row in the real table.

    -- OR --
    2.- Just get another dump from DB2 where it create single file for each lob.
    :p
  • 2. Re: sqlldr with CLOBs in single file problem
    970752 Newbie
    Currently Being Moderated
    Thanks for your advice. But here is the problem...The table has almost 8 million rows so the thought of creating 8 million files is not comforting.
    I will do that as a last resort but the OS (Linux) will start having inode problems with that many files to deal with.

    I thought there would be a parameter in sqlldr for this case.

    -Hammer

    Edited by: Hammer11 on Nov 9, 2012 6:08 PM
  • 3. Re: sqlldr with CLOBs in single file problem
    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.

    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
  • 4. Re: sqlldr with CLOBs in single file problem
    Jorden M Newbie
    Currently Being Moderated

    Someone please reply to this post with the possible solution, as i also get stuck with the same problem.

  • 5. Re: sqlldr with CLOBs in single file problem
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    Pl create a new thread for your issue instead on hijacking a dead one

Legend

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