This discussion is archived
5 Replies Latest reply: Dec 7, 2012 12:56 AM by 595882 RSS

SQL LOADER USING EXTRNAL TABLE

595882 Newbie
Currently Being Moderated
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
    595882 Newbie
    Currently Being Moderated
    Version 10.2.0.3.0
    but need oracle 9i compatibilty
  • 2. Re: SQL LOADER USING EXTRNAL TABLE
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    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
    595882 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    595882 Newbie
    Currently Being Moderated
    I have already tried that but same result

Legend

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