EXTERNAL TABLE ERROR

user12121820

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.

Solomon Yakobson

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

Solomon Yakobson

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

user12121820

Thanks that worked for me.