Hi I need to load the data from CSV file to oracle tablesusing sql loader. A coulmn CRE_TIME in the CSV file has the possibilty of having either a null value or 00:00:00 or a valid timestamp. But the column CRE_TIME is defined with data type INTEGER EXTERNAL.So i need to replace null or 00:00:00 with a value 0 in the oracle table.

my control file looks like
CRE_TIME "Decode(:CRE_TIME,null,replace(TO_DATE('00:00:00','HH24:MI:SS'),':',''),'00:00:00',replace(TO_DATE('0 0:00:00','HH24:MI:SS'),':',''),replace(TO_DATE(:CRE_TIME,'HH24:MI:SS'),':',''))",

But still it is not working.(Error:ORA-01722: invalid number) Please advice on what could be done.

Please note that the datatype for coulmn is INTEGER EXTERNAL