oracle external table with date column and skip header

Question

I have a file,

ID,DNS,R_D,R_A
1,123456,2014/11/17,10
2,987654,2016/05/20,30
3,434343,2017/08/01,20

that I'm trying to load to oracle using External Tables. I have to skip the header row and also load the date column.

This is my query:

DECLARE
FILENAME VARCHAR2(400);
BEGIN
FILENAME := 'actual_data.txt';
EXECUTE IMMEDIATE 'CREATE TABLE EXT_TMP (
  ID      NUMBER(25),
  DNS  VARCHAR2(20),
  R_D DATE,
  R_A  NUMBER(25)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY USER_DIR
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY '',''
    MISSING FIELD VALUES ARE NULL
    SKIP 1
    (
  "ID",      
  "DNS",
  "R_D" date "dd-mon-yy",
  "RECHARGE_AMOUNT"
    )
  )
  LOCATION (''' || FILENAME || ''')
)
PARALLEL 5
REJECT LIMIT UNLIMITED';
END;

I get following exception:

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 "skip": expecting one of: "column, exit, (,
reject"
KUP-01007: at line 4 column 5
ORA-06512: at "SYS.ORACLE_LOADER", line 19

I'm using sqlplus.

Could some oracle veterans please help me out and tell me what I'm doing wrong here? I'm very new to oracle.

Answer 1

You don't want to create any kind of tables (including external ones) in PL/SQL; not that it is impossible, but it is opposite of the best practices.

Have a look at my attempt, based on information you provided - works OK.

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> create table ext_tmp
  2  (
  3    id      number,
  4    dns     varchar2(20),
  5    r_d     date,
  6    r_a     number
  7  )
  8  organization external
  9  (
 10    type oracle_loader
 11    default directory kcdba_dpdir
 12    access parameters
 13    (
 14      records delimited by newline
 15      skip 1
 16      fields terminated by ',' lrtrim
 17      missing field values are null
 18      (
 19       id,
 20       dns,
 21       r_d date 'yyyy/mm/dd',
 22       r_a
 23      )
 24    )
 25    location ('actual_data.txt')
 26  )
 27  parallel 5
 28  reject limit unlimited;

Table created.

SQL> select * from ext_tmp;

        ID DNS                  R_D               R_A
---------- -------------------- ---------- ----------
         1 123456               17.11.2014         10
         2 987654               20.05.2016         30
         3 434343               01.08.2017         20

SQL>

Answer 2

In my case skip 1 didn't work even with placing it between records delimited by newline and fields terminated by ',' lrtrim until I used load when. Now skip 1 works with the following access parameters:

access parameters (
    records delimited by newline 
    load when (someField != BLANK)
    skip 1 
    fields terminated by '','' lrtrim
    missing field values are null
    reject rows with all null fields
)