Decode function not working in SQL*Loader

1
votes

I am using SQL*Loader to import data from CSV to an Oracle table. My data has multiple date fields and i need to replace null values with '0001-01-01-00-00-00' and other values need to follow date format.

I used decode option like

decode(:QUOTE_CREATE_DT,NULL,'0001-01-01-00.00.00',
  TO_DATE(:QUOTE_CREATE_DT,'MM/DD/YYYY HH24:MI:SS'))

which is not working when null values occur.

SQL*Loader error:

Record 2: Rejected - Error on table EX_QUOTE_MO_SAMPLE, column QUOTE_CREATE_DT.
ORA-01847: day of month must be between 1 and last day of month
1
oraclesql-loader

1 Answers

0
votes

Your version is relying on implicit conversion using the SQL*Loader session's NLS settings.

You could use decode or coalesce or nvl to provide a date string in the format you're converting, but inside the to_date() call:

QUOTE_CREATE_DT "TO_DATE(NVL(:QUOTE_CREATE_DT, '01/01/0001'), 'MM/DD/YYYY')"