external table in oracle?

Question

hi friends i have tried to load data from flat file to external file using oralce_loader access driver my code is

create  table test_ext (
    id_1 varchar(35),
    emp_name varchar(25),
    e_mail varchar(25)) 
organization external (
    type oracle_loader default directory new_dir access parameters
        ( records delimited by newline fields(
             id_1 char(30),
             e_name char(25),
             mail char(25)))
    location('test.csv')) reject limit unlimited;

and my data file:

"E.FIRST_NAME||','||E.EMAIL||','||MANAGER_ID"
-----------------------------------------------
"Jennifer,JWHALEN,101"
"Michael,MHARTSTE,100"
"Susan,SMAVRIS,101"
"Hermann,HBAER,101"
"Shelley,SHIGGINS,101"
"William,WGIETZ,205"
"Steven,SKING,"
"Neena,NKOCHHAR,100"
"Lex,LDEHAAN,100"
"Alexander,AHUNOLD,102"
"Bruce,BERNST,103"
"David,DAUSTIN,103"
"Valli,VPATABAL,103"
"Diana,DLORENTZ,103"
"Nancy,NGREENBE,101"
"Daniel,DFAVIET,108"
"John,JCHEN,108"

while run that above query i got

**ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04043: table column not found in external source: EMP_NAME
29913. 00000 -  "error in executing %s callout"
*Cause:    The execution of the specified callout caused an error.
*Action:   Examine the error messages take appropriate action.**

I tried so many things but I can't.

Answer 1

firstly your csv file looks wrong.

"Alexander,AHUNOLD,102"

remove all quotes, otherwise it will look like one field.

secondly your using the "fields" syntax suggesting you want fixed length csv file, but your dat file is comma seperated. so i think you want to fix your csv to remove the quotes and the two spurious header lines and change your table DDL to :

create  table test_ext (
    id_1 varchar(35),
    emp_name varchar(25),
    e_mail varchar(25)) 
organization external (
    type oracle_loader default directory new_dir access parameters
        ( 
         records delimited by newline 
         fields terminated by ',' optionally enclosed by '"')
    location('test.csv')) reject limit unlimited;

eg:

SQL> host cat test.csv
Jennifer,JWHALEN,101
Michael,MHARTSTE,100
Susan,SMAVRIS,101
Hermann,HBAER,101
Shelley,SHIGGINS,101
William,WGIETZ,205
Steven,SKING,
Neena,NKOCHHAR,100
Lex,LDEHAAN,100
Alexander,AHUNOLD,102
Bruce,BERNST,103
David,DAUSTIN,103
Valli,VPATABAL,103
Diana,DLORENTZ,103
Nancy,NGREENBE,101
Daniel,DFAVIET,108
John,JCHEN,108
SQL> create  table test_ext (
  2      id_1 varchar(35),
  3      emp_name varchar(25),
  4      e_mail varchar(25))
  5  organization external (
  6      type oracle_loader default directory new_dir access parameters
  7          (
  8           records delimited by newline
  9           fields terminated by ',' optionally enclosed by '"')
 10      location('test.csv')) reject limit unlimited;

Table created.

SQL> select * from test_ext;

ID_1                                EMP_NAME                  E_MAIL
----------------------------------- ------------------------- -------------------------
Jennifer                            JWHALEN                   101
Michael                             MHARTSTE                  100
Susan                               SMAVRIS                   101
Hermann                             HBAER                     101
Shelley                             SHIGGINS                  101
William                             WGIETZ                    205
Neena                               NKOCHHAR                  100
Lex                                 LDEHAAN                   100
...etc...