sqlldr not loading dates - ORA-01843: not a valid month
635935Jan 2 2013 — edited Jan 2 2013Here is my SQL Loader CTL File:
load data
infile *
Append into table SOME_FILES
fields terminated by "," Optionally Enclosed by '"'
TRAILING NULLCOLS
( FILE_NAME, FILE_SIZE INTEGER, FILE_MODIFIED DATE 'MM-DD-YYYY',FILE_RECEIVED DATE 'MM/DD/YYYY')
BEGINDATA
file.txt,361662,01-01-2012,01/01/2012
This give me an "invalid month" error message.
Record 1: Rejected - Error on table DC_GEF_834_FILES, column FILE_MODIFIED.
ORA-01843: not a valid month
If I connect using sqlplus and perform this insert command, it works
SQL> insert into SOME_FILES (FILE_NAME, FILE_SIZE, FILE_MODIFIED, FILE_RECEIVED) VALUES ('file.txt',361662,TO_DATE('01-01-2012','MM-DD-YYYY'),TO_DATE( '01/01/2012', 'MM/DD/YYYY') );
I've tried many different formats for data and for the CTL parameters, include "DATE EXTERNAL" "TIMESTAMP" and also just saying "DATE" and formatting the data to match the NLS_DATE_FORMAT parameter value. The error doesn't change.
Any ideas?