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.
INTO TABLE RE_STATS_STAGING
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
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,