SQL Loader date format

7
votes

Hi i am trying to load data from a file into a table in Oracle but i am recieving a error ORA-01843: not a valid month when inserting a date.

The date in the file is held as 27-01-2014

The format in my table is set to DATE.

This is the control file.

load data
infile 'file1.csv'
append
into table my_table
fields terminated by ',' TRAILING NULLCOLS
(Case_reference, Attempt_Number, Dialled_Number, Date_Called)

Any one know where im going wrong?

Cheers

1
sqldatabaseoracledate

1 Answers

10
votes

Oracle may take 27 as a month and it throws such error

Try to give the format in the control file itself

load data
infile 'file1.csv'
append
into table my_table
fields terminated by ',' TRAILING NULLCOLS
(Business_function, 
Case_reference, 
Sub_sequence, 
Dialler_Master_Stream ,
Dialler_Call_Stream,    
Dialler_Super_Stream, 
Attempt_Number, 
Dialled_Number, 
Date_Called DATE "DD-MM-YYYY")