Re: SQL*Loader Date error (ORA-01858)
brian x wisniewski
Madhavi, did you get this figured out?
I hadn't seen a reply on the list.
When I run into issues like this I typically
create a table with all varchar fields and load into that table to check
if what I'm loading into the columns is what I expected to. Often
it's not and adjustments need to be made in the control file.
I also prefer external tables - just
preference. I create the external table as varchars and then do the
manipulation on the fields during the select from the external.
rbld=`$SQL >> $OUT_FILE <<
directory $BASE_DIR_NAME as '$BASE_DIR';
directory $LOG_DIR_NAME as '$LOG_DIR';
read,write on directory $BASE_DIR_NAME to $OWNER, bwisniewski;
read,write on directory $LOG_DIR_NAME to $OWNER, bwisniewski;
(records fixed 99 delimited by newline
load when (ban notequal blanks and subscriber notequal
reject limit unlimited;
This is where I do the formatting and
conversion to a number..
into $TABLE (ban, name, address, city, state, zip, subscriber, balance)
select ban, name, address, city, state, zip,
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
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
INTO TABLE RE_STATS_STAGING
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED
TIMESTAMP CONSTANT "to_date('_when_changed_','YYYYMMDDHH24MISS')"
Any ideas on where I am going wrong? Any
thoughts or help will be appreciated.
||Madhavi Kanugo <MKanugo@ibasis.net>
Sent by: firstname.lastname@example.org
12/07/2005 01:19 PM
Please respond to MKanugo
SQL*Loader Date error (ORA-01858)
Thanks in advance,