CODEWITHSUNDEEP
sqloracle-databasetimestampsql-loadertimestamp-with-timezone
stk
stk

Reputation: 1

Headache loading timestamp with Oracle Sqlldr

Hi All i'm having an issue loading a file with timestamp values with sqlldr, can anyone please help with an idea? This is what a line of my file to load with the timestamp looks like : ..... 2014-09-02-00.00.00.

And my ctrl file : ... ... Field POSITION(1085) TIMESTAMP )

Every record is rejected with the error: ORA-26041: DATETIME/INTERVAL datatype conversion error

Upvotes: 0

Views: 4720

Answers (1)

Alex Poole
Alex Poole

Reputation: 191435

SQL*Loader will try to interpret the string as a timestamp using your default NLS session parameters, which by default will be something like:

select value from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT';

VALUE
--------------------------------------------------------------------------------
DD-MON-RR HH24.MI.SSXFF

... although yours appears to be something else as that setting gives ORA-01843: not a valid month' with the example string. The log file from the load will also show the format it tried to apply:

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
"<field>"                            1085     *   ,       DATETIME DD-MON-RR HH24.MI.SSXFF

Whatever your default format is, it doesn't seem to match the format in the file. You could change the format for the database or with a logon trigger but that's not going to be helpful if you have other sources or outputs that expect the existing format; and you shouldn't rely on NLS settings anyway as they could be different in another environment.

You can supply the timestamp format as part of the field definition:

... Field POSITION(1085) TIMESTAMP "YYYY-MM-DD-HH24.MI.SS")

If your column is timestamp with time zone - suggested by the tags but not the question - then it will be inserted in your session's time zone.

It's more common to specify the position range, though just providing the start position 1085 will work if this is the last field in the record.

Upvotes: 2

Related Questions