Re: SQL*Loader Date error (ORA-01858)

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 << !REBUILD

        create directory $BASE_DIR_NAME as '$BASE_DIR';
        create directory $LOG_DIR_NAME as '$LOG_DIR';

        grant read,write on directory $BASE_DIR_NAME to $OWNER, 
bwisniewski;
        grant read,write on directory $LOG_DIR_NAME to $OWNER, 
bwisniewski;

        create table $EXT_TABLE
                (BAN    varchar2(9),
                NAME      varchar2(20),
                ADDRESS varchar2(25),
                CITY    varchar2(12),
                STATE   varchar2(2),
                ZIP     varchar2(5),
                SUBSCRIBER varchar2(10),
                BALANCE varchar2(8))
                organization external
                (type oracle_loader
                        default directory $BASE_DIR_NAME
                        access parameters
                                (records fixed 99 delimited by newline
                                badfile $LOG_DIR_NAME:'$BAD_FILE'
                                logfile $LOG_DIR_NAME:'$LOG_FILE'
                                discardfile $LOG_DIR_NAME:'$DIS_FILE'
                                load when (ban notequal blanks and 
subscriber notequal blanks)
                                fields lrtrim(
                                BAN (1:+9),
                                NAME (*:+20),
                                ADDRESS (*:+25),
                                CITY (*:+12),
                                STATE (*:+2),
                                ZIP (*:+5),
                                SUBSCRIBER (*:+10),
                                BALANCE (*:+8)))
                                location ($BASE_DIR_NAME:'$FTP_FILE'))
                                reject limit unlimited;

!REBUILD`

...

This is where I do the formatting and conversion to a number..

        insert into $TABLE (ban, name, address, city, state, zip, 
subscriber, balance)
                select ban, name, address, city, state, zip, subscriber, 
to_number(ltrim(balance,'0'))/100 
                from $EXT_TABLE;

- Brian






Madhavi Kanugo <MKanugo@xxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
12/07/2005 01:19 PM
Please respond to MKanugo
 
        To:     "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
        cc: 
        Subject:        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

Other related posts: