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

From: <brian.x.wisniewski_at_jpmchase.com>
Date: Fri, 9 Dec 2005 10:51:42 -0500
Message-ID: <OFC828D3FD.24DC43CC-ON852570D2.005428FE-852570D2.00572193@jpmchase.com>

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_at_ibasis.net>
Sent by: oracle-l-bounce_at_freelists.org
12/07/2005 01:19 PM
Please respond to MKanugo  

        To:     "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>
        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    

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

Received on Fri Dec 09 2005 - 09:55:01 CST