SQL loader date issue

Question

Below is the Sql loader control script which I'm trying to use to load data from the input file to Oracle. I am having issue with COL3 which is of date datatype in DATATABLE. The data is in Timestamp format in the file.

Error I'm getting is "ORA-00907: missing right parenthesis".

load data
    INFILE 'C:\datafile.txt'
    INTO TABLE DATATABLE
    APPEND
    FIELDS TERMINATED BY '|' 
    (
     COL1,
    X FILLER,
    COL2 FLOAT,
    Y FILLER,
    Z FILLER,
    COL3 DATE(10) "TRUNC(TO_TIMESTAMP(:COL3,'MM/DD/YYYY'))",
    COL4, 
    COL5 "MM/DD/YYYY HH:MI:SS PM")

// C:\datafile.txt

1234567|.59999999999999999|.39999999999999999|240.74000000000001|USER1|11/29/2016 12:00:00 AM|USER1|11/29/2016 12:00:00 AM

I tested the output with below sql which seems to be correct:

select TRUNC(to_timestamp('JAN 29 2016 12:00:00 AM', 'MON DD YYYY HH:MI:SS:FF3 PM')) FROM DUAL ;

Any help would be appreciated.

Well on the line with COL3 the closing parenthesis for the trunc is missing. not sure it's the only problem but this one is obvious — LauDec

LauDec LauDec · Accepted Answer · 2017-08-02T22:29:32

try like that ( I can't try it on this computer )

load data
    INFILE 'C:\datafile.txt'
    INTO TABLE DATATABLE
    APPEND
    FIELDS TERMINATED BY '|' 
    (
     COL1,
    X FILLER,
    COL2 FLOAT,
    Y FILLER,
    Z FILLER,
    COL3 DATE 'MM/DD/YYYY HH:MI:SS AM',
    COL4, 
    COL5 DATE 'MM/DD/YYYY HH:MI:SS AM')

The date format that you have in your datafile is not a timestamp. It's a normal date format for oracle. So you don't have to try to cast it as timestamp

SQL loader date issue

1 Answers