5 Replies Latest reply: Oct 21, 2013 10:24 AM by Jorden M RSS

    sqlldr with CLOBs in single file problem

    970752
      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
          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
            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
              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

                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

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