5 Replies Latest reply: Dec 7, 2012 2:56 AM by user592879 RSS

    SQL LOADER USING EXTRNAL TABLE

    user592879
      I have .csv file having around 70k records
      in which fields are delimited by tab and
      enclosed in double quotes but double quotes may be part of data.
      and records are delimited by newline.
      After creating external table when I issue SELECT statment
      select count(*) from proTxt ;
      ERROR at line 1:
      ORA-29913: error in executing ODCIEXTTABLEOPEN callout
      ORA-29400: data cartridge error
      KUP-04020: found record longer than buffer size supported, 524288, in C:\Program Files\Apache Software Foundation\Tomcat
      5.5\webapps\tmTest\upload\product\Data\output09_1.txt
      ORA-06512: at "SYS.ORACLE_LOADER", line 19

      Following is the create table statement:
      CREATE TABLE proTxt (PRO_CODE VARCHAR2(30),
      PRO_DESC VARCHAR2(500),
      PUR_PRICE VARCHAR2(20),
      SALE_PRICE VARCHAR2(20)
      )
      ORGANIZATION EXTERNAL
      (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY DAT_DIR
      ACCESS PARAMETERS
      (
      records delimited by NEWLINE SKIP 1

      badfile BAD_DIR:'proTxt%a_%p.bad'
      logfile LOG_DIR:'proTxt%a_%p.log'
      fields terminated by X'9' OPTIONALLY ENCLOSED BY '"' AND '"'
      missing field values are null
      ( PRO_CODE,
      PRO_DESC,
      PUR_PRICE,
      SALE_PRICE
      )
      )
      LOCATION ('output09_1.txt')
      )
      PARALLEL 4
      REJECT LIMIT UNLIMITED;

      record size is not large.
      Log file :

      LOG file opened at 12/05/12 20:25:40

      KUP-04020: found record longer than buffer size supported, 524288, in C:\Program Files\Apache Software Foundation\Tomcat 5.5\webapps\tmTest\upload\product\Data\output09_1.txt
      KUP-04053: record number 2

      data file
      PRO_CODE     PRO_DESC     PUR_PRICE     SALE_PRICE
      "0000336658"     "BEARING"     "Rs.0.00"     "Rs.0.00"
      "0000790028"     "SEAL"     "Rs.76.00"     "Rs.90.00"
      "0000790118"     "SPRING"     "Rs.24.00"     "Rs.28.00"
      "0000792284"     "F.BRK.CAL.W/O PA"     "Rs.2,627.00"     "Rs.3,100.00"
      "0000792285"     "F.BRK.CAL.W/O PA"     "Rs.2,627.00"     "Rs.3,100.00"
      "0005896322"     "PISTON, RING"     "Rs.5,000.00"     "Rs.5,900.00"
      "0005896323"     "PISTONS, RINGS AND P"     "Rs.17,755.00"     "Rs.20,951.00"
      "0005896559"     "PISTON, RINGS AND PI"     "Rs.5,000.00"     "Rs.5,900.00"
        • 1. Re: SQL LOADER USING EXTRNAL TABLE
          user592879
          Version 10.2.0.3.0
          but need oracle 9i compatibilty
          • 2. Re: SQL LOADER USING EXTRNAL TABLE
            Srini Chavali-Oracle
            Pl see if these MOS Docs can help

            Errors ORA-29400 KUP-4020 While Querying Huge Records From External Table [ID 466277.1]
            Using External Table with Flatfile Moved Across Platforms. [ID 806048.1]

            HTH
            Srini
            • 3. Re: SQL LOADER USING EXTRNAL TABLE
              user592879
              Hi,

              when i used
              records delimited by *'\r'*
              then 4226 record written to table
              but enclosed charcter double quotes["] were also written and
              there is some space between charcters


              " 0 0 0 0 3 3 6 6 5 8 " " B E A R I N G " " R s . 0 . 0 0 " " R s . 0 . 0 0 "
              " 0 0 0 0 8 5 6 7 0 7 " " P L U G " " R s . 0 . 0 0 " " R s . 0 . 0 0 "

              Definitely this is "External Table with Flatfile Moved Across Platforms" issue.
              when I opened .csv file in excel and saved as tab delimited it works fine.
              But I do not know plateform of data file.
              How to know the CHARACTERSET of data file

              Log file

              Field Definitions for table PROTXT
              Record format DELIMITED, delimited by

              Data in file has same endianness as the platform
              Rows with all null fields are accepted

              Fields in Data Source:

              PRO_CODE CHAR (255)
              Terminated by "9"
              Enclosed by """ and """
              Trim whitespace same as SQL Loader
              PRO_DESC CHAR (255)
              Terminated by "9"
              Enclosed by """ and """
              Trim whitespace same as SQL Loader
              PUR_PRICE CHAR (255)
              Terminated by "9"
              Enclosed by """ and """
              Trim whitespace same as SQL Loader
              SALE_PRICE CHAR (255)
              Terminated by "9"
              Enclosed by """ and """
              Trim whitespace same as SQL Loader
              • 4. Re: SQL LOADER USING EXTRNAL TABLE
                971895
                Try like...


                CREATE TABLE proTxt
                (PRO_CODE VARCHAR2(30),
                PRO_DESC VARCHAR2(500),
                PUR_PRICE VARCHAR2(20),
                SALE_PRICE VARCHAR2(20)
                )
                ORGANIZATION EXTERNAL
                (
                TYPE ORACLE_LOADER
                DEFAULT DIRECTORY AD_DATA_EXT
                ACCESS PARAMETERS
                (
                records delimited by NEWLINE SKIP 1
                FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
                missing field values are null
                ( PRO_CODE,
                PRO_DESC,
                PUR_PRICE,
                SALE_PRICE
                )
                )
                LOCATION ('test.txt')
                )
                PARALLEL 4
                REJECT LIMIT UNLIMITED;
                • 5. Re: SQL LOADER USING EXTRNAL TABLE
                  user592879
                  I have already tried that but same result