SQL loader date format

Question

Hi, I am trying to load data from a file into a table in Oracle, but I get error ORA-01843: Invalid month when inserting date.

The date in the file is stored as 01/27/2014

The format in my table is set to DATE.

This is a 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)

Does anyone know where I am going wrong?

Greetings

Answer 1

Oracle may take 27 as a month, and this generates such an error

Try to specify 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")