ORA-26041: DATETIME/INTERVAL datatype conversion error

Question

I'm trying to load data in to a database using SQL-Loader but I'm (as many others) having a problem with my time format. Due to my limited skills with SQL-Loader I've not been able to find a previous post that would aid me. So here goes.

The data that I'm trying to insert is:

"some text"|"some text"|"some text"|"23-APR-12 12.00.00.000000000 AM"|"some text"|"some text."|"TEXT"|"23-APR-12 12.00.00.000000000 AM"|"some text"|"some text"|"some text"|"some text"|0|

The CTL file looks like this:

OPTIONS (ERRORS=1586)
LOAD DATA 
INFILE 'SOURCE.ldr' "str '{EOL}'"
APPEND
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE "DATABASE_NAME"."TABLE"
FIELDS TERMINATED BY'|'
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS ( 
"DOMAIN" ,
"CONTEXT" ,
"KEY" ,
"CREATED" TIMESTAMP "DD-MON-RR HH.MI.SSXFF AM" ,
"DESCRIPTION" ,
"VALUE" ,
"TYPE" ,
"CHANGED" TIMESTAMP "DD-MON-RR HH.MI.SSXFF AM" ,
"CORPORATEENGLISHVALUE" ,
"CORPORATEVALUE" ,
"ENGLISHVALUE" ,
"OLDDBKEY" ,
"FAVOURITE" )

And the Table:

DOMAIN                  VARCHAR2(255 BYTE)
CONTEXT                 VARCHAR2(255 BYTE)
KEY                     VARCHAR2(255 BYTE)
CREATED                 TIMESTAMP(6)
DESCRIPTION             VARCHAR2(255 BYTE)
VALUE                   VARCHAR2(4000 BYTE)
TYPE                    VARCHAR2(255 BYTE)
CHANGED                 TIMESTAMP(6)
CORPORATEENGLISHVALUE   VARCHAR2(4000 CHAR)
CORPORATEVALUE          VARCHAR2(4000 CHAR)
ENGLISHVALUE            VARCHAR2(4000 CHAR)
OLDDBKEY                VARCHAR2(35 BYTE)
FAVOURITE               NUMBER(1,0)

And the Oracle database has these NLS:

Date Format:         RRRR-MM-DD
Timestamp Format:    RRRR-MM-DD HH24:MI:SSXFF
Timestamp TZ Format: RRRR-MM-DD HH24:MI:SSXFF TZR

The error I'm getting is:

Record 1: Rejected - Error on table "DATABASE_NAME"."TABLE", column "CREATED".
ORA-26041: DATETIME/INTERVAL datatype conversion error

If I manually modify the timestamp from 23-APR-12 12.00.00.000000000 AM to 23-APR-12 12.00.00 AM it works fine. So my guess is that there's something wrong with the "CREATED" TIMESTAMP "DD-MON-RR HH.MI.SSXFF AM". I noticed as well that there are errors with the same ORA-26041 when months are displayed different. In English October is show as OCTand in Swedish its OKT. If I manually change all English versions of the months in the data file, the script works.

Answer 1

First I'll give @AlexPoole cred for this. He pointed me in the right way.

I'm sitting on a windows computer and the example was from Linux. The correct input is:

set NLS_DATE_LANGUAGE=AMERICAN

Please bear in mind that you can't have any white space in the NLS_DATE_LANGUAGE=AMERICAN. I did an settings export from the target database to see what NLS settings it used so I could replicate it. I made a simple copy and paste mistake. The print out was done like this: NLS_DATE_LANGUAGE = "AMERICAN" when it should be NLS_DATE_LANGUAGE=AMERICAN. This solved the problem with English dates transformed into Swedish dates. I guess that it should work for anyone that has a different spelling of months.

Second, To get the database to accept the time 23-APR-12 12.00.00.000000000 AM with out generating an error was solved like this:

From: "CREATED" TIMESTAMP "DD-MON-RR HH.MI.SSXFF AM"
To:   "CREATED" TIMESTAMP "DD-MON-RR HH.MI.SS.FF AM"

So to wrap this up.

To get English months (OCT, MAY etc) in to a non English database in a windows environment, use: set NLS_DATE_LANGUAGE=AMERICAN. And the correct time format for 23-APR-12 12.00.00.000000000 AM is "DD-MON-RR HH.MI.SS.FF AM".

Hope this helps anyone else.