SQL*Loader Date error (ORA-01858)

Hello All,  

I'm trying to load data using SQL Loader and ran into the ORA-01858: a non-numeric character was found where a numeric was expected error.  

Below is my control file. As you can see, the timestamp is a constant field and I am loading its value from the filename of the data files. I am executing the sql loader from a shell script and creating the control file on the fly. The filename is in the form of: 20051206130101.txt So basically, the shell script replaces the when_changed value with 20051206130101.  

I created a temp table with just a date field and tried to insert a value into it. Insert into temp values
(to_date('20051207160752','YYYYMMDDHH24MISS')); and there is no error and
date conversion is implict.  

But SQLLOADER is erring out on ORA-01858.  

LOAD DATA INFILE FILENAME APPEND INTO TABLE RE_STATS_STAGING FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'     TRAILING NULLCOLS
(

  RE_NAME,   TG_TYPE,   TRUNKGROUP,   CONNECTS,   HANGUPS,   DECLINES,             LOOPS,             SECONDS,             CC_CONNECTED,             CC_SETUP,             TIMESTAMP CONSTANT
"to_date('_when_changed_','YYYYMMDDHH24MISS')"

        )  

Any ideas on where I am going wrong? Any thoughts or help will be appreciated.

Thanks in advance,

Madhavi    

--
http://www.freelists.org/webpage/oracle-l