Luke SpringWalker Luke SpringWalker - 2 months ago 20
SQL Question

Oracle loader - error defining access parameters (KUP-01005)

I'm having problem querying the following table loaded from a file.

CREATE TABLE "testTable"
(
"EAN" NUMBER(38,0),
"STOCK" NUMBER(38,0),
"SECCION" NUMBER(38,0)
)
organization external
( default directory xtern_data_dir
access parameters
( fields terminated by ';'
badfile xtern_data_dir:'testTable.bad'
logfile xtern_data_dir:'testTable.log'
discardfile xtern_data_dir:'testTable.dsc'
)
location ('0025_STOCK.csv')
)


But I'm getting the following error:

KUP-01005: syntax error: found "badfile": expecting one of: "column, enclosed, (, ltrim, lrtrim, ldrtrim, missing, notrim, optionally, rtrim, reject"


I have tried removing badfile, logfile and discardfile, but then I get another error, and I'm not sure how to make it work.

Please, help! Thanks in advance.

Answer

Try to use

 CREATE TABLE "testTable" 
        (  
          "EAN" NUMBER(38,0), 
          "STOCK" NUMBER(38,0), 
          "SECCION" NUMBER(38,0) 
        )
        organization external
          ( default directory xtern_data_dir
            access parameters
              ( RECORDS DELIMITED BY newline
                badfile xtern_data_dir:'testTable.bad'
                logfile xtern_data_dir:'testTable.log'
                discardfile xtern_data_dir:'testTable.dsc'
                fields terminated by ';'
              )
        location ('0025_STOCK.csv')  
      )