Forum Stats

  • 3,782,328 Users
  • 2,254,637 Discussions
  • 7,880,045 Comments

Discussions

EXTERNAL TABLE ERROR

user12121820
user12121820 Member Posts: 27

Hi ,

I am using below extrenal table script

CREATE TABLE emp_load

     (employee_number      VARCHAR2(50),

      employee_last_name   VARCHAR2(50),

       employee_first_name  VARCHAR2(50),

      employee_middle_name VARCHAR2(50),

       employee_hire_date   VARCHAR2(50))

    ORGANIZATION EXTERNAL

      (TYPE ORACLE_LOADER

      DEFAULT DIRECTORY ABC_LOAD

      ACCESS PARAMETERS (FIELDS TERMINATED BY '|'

        RECORDS DELIMITED BY NEWLINE

        MISSING FILED VALUES ARE NULL

         (employee_number      VARCHAR2(50),

      employee_last_name   VARCHAR2(50),

       employee_first_name  VARCHAR2(50),

      employee_middle_name VARCHAR2(50),

       employee_hire_date   VARCHAR2(50))

       )         

      LOCATION ('info.dat')

     ) REJECT LIMIT UNLIMITED;

and have created a directory ABC_LOAD as "C:\Oracle" and my os is WINDOWS 8.

and the content of my .DAT file is

010|ABC|DEF|XYZ|03-DEC-2011

but when i doing select * from emp_load i am getting below error.

ERROR at line 1:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-29400: data cartridge error

KUP-00554: error encountered while parsing access parameters

KUP-01005: syntax error: found "records": expecting one of: "column, enclosed,

(, ltrim, lrtrim, ldrtrim, missing, notrim, optionally, rtrim, reject"

KUP-01007: at line 2 column 9

I am using Oracle 11g R2 and Windows as OS.

Any help please.

Thanks.

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,014 Red Diamond
    edited May 31, 2014 9:34AM Accepted Answer

    1. Typo in MISSING FILED VALUES ARE NULL

    2. RECORDS DELIMITED BY NEWLINE must preceed FIELDS TERMINATED BY '|'

    3. There is no such SQL*Loader/External table field type VARCHAR2 (in this case you don't even need to specify it)

    SQL> CREATE TABLE emp_load
      2       (employee_number      VARCHAR2(50),
      3        employee_last_name   VARCHAR2(50),
      4         employee_first_name  VARCHAR2(50),
      5        employee_middle_name VARCHAR2(50),
      6         employee_hire_date   VARCHAR2(50))
      7      ORGANIZATION EXTERNAL
      8        (TYPE ORACLE_LOADER
      9        DEFAULT DIRECTORY TEMP
    10        ACCESS PARAMETERS (
    11          RECORDS DELIMITED BY NEWLINE
    12          FIELDS TERMINATED BY '|'
    13          MISSING FIELD VALUES ARE NULL
    14           (employee_number,
    15        employee_last_name,
    16         employee_first_name,
    17        employee_middle_name,
    18         employee_hire_date)
    19         )
    20        LOCATION ('info.dat')
    21       ) REJECT LIMIT UNLIMITED
    22  /

    Table created.

    SQL> SELECT  *
      2    FROM  emp_load
      3  /

    EMPLOYEE_N EMPLOYEE_L EMPLOYEE_F EMPLOYEE_M EMPLOYEE_HI
    ---------- ---------- ---------- ---------- -----------
    010        ABC        DEF        XYZ        03-DEC-2011

    SQL>

    SY

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,014 Red Diamond
    edited May 31, 2014 9:34AM Accepted Answer

    1. Typo in MISSING FILED VALUES ARE NULL

    2. RECORDS DELIMITED BY NEWLINE must preceed FIELDS TERMINATED BY '|'

    3. There is no such SQL*Loader/External table field type VARCHAR2 (in this case you don't even need to specify it)

    SQL> CREATE TABLE emp_load
      2       (employee_number      VARCHAR2(50),
      3        employee_last_name   VARCHAR2(50),
      4         employee_first_name  VARCHAR2(50),
      5        employee_middle_name VARCHAR2(50),
      6         employee_hire_date   VARCHAR2(50))
      7      ORGANIZATION EXTERNAL
      8        (TYPE ORACLE_LOADER
      9        DEFAULT DIRECTORY TEMP
    10        ACCESS PARAMETERS (
    11          RECORDS DELIMITED BY NEWLINE
    12          FIELDS TERMINATED BY '|'
    13          MISSING FIELD VALUES ARE NULL
    14           (employee_number,
    15        employee_last_name,
    16         employee_first_name,
    17        employee_middle_name,
    18         employee_hire_date)
    19         )
    20        LOCATION ('info.dat')
    21       ) REJECT LIMIT UNLIMITED
    22  /

    Table created.

    SQL> SELECT  *
      2    FROM  emp_load
      3  /

    EMPLOYEE_N EMPLOYEE_L EMPLOYEE_F EMPLOYEE_M EMPLOYEE_HI
    ---------- ---------- ---------- ---------- -----------
    010        ABC        DEF        XYZ        03-DEC-2011

    SQL>

    SY

  • Thanks that worked for me.

This discussion has been closed.