From oracle-l-bounce@freelists.org Fri Dec 9 09:55:01 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id jB9Ft1KQ017118 for ; Fri, 9 Dec 2005 09:55:01 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id jB9FswAX017109 for ; Fri, 9 Dec 2005 09:54:58 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7E43D24ECF9; Fri, 9 Dec 2005 10:54:50 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16925-01; Fri, 9 Dec 2005 10:54:50 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E074072ECB; Fri, 9 Dec 2005 10:54:49 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 09 Dec 2005 10:54:49 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8EFE424CD9F; Fri, 9 Dec 2005 10:54:49 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16842-10; Fri, 9 Dec 2005 10:54:49 -0500 (EST) Received: from sb10.jpmchase.com (mailms.chase.com [170.148.48.205]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2F8BB72ECB; Fri, 9 Dec 2005 10:54:49 -0500 (EST) Received: from jpmchase.com ([10.246.2.156]) by sb10.jpmchase.com (Switch-3.1.6/Switch-3.1.6) with ESMTP id jB9FdK1J001883; Fri, 9 Dec 2005 10:39:20 -0500 Received: from ([10.21.212.168]) by imb2.jpmchase.com with ESMTP id KP-BRCFP.23809465; Fri, 09 Dec 2005 10:53:24 -0500 In-Reply-To: To: MKanugo@ibasis.net Cc: "'oracle-l@freelists.org'" , oracle-l-bounce@freelists.org Subject: Re: SQL*Loader Date error (ORA-01858) MIME-Version: 1.0 From: brian.x.wisniewski@jpmchase.com Message-ID: Date: Fri, 9 Dec 2005 10:51:42 -0500 X-MIMETrack: Serialize by Router on MIUSMF009/JPMCHASE(Release 6.5.3|September 14, 2004) at 12/09/2005 10:54:26 AM, Serialize complete at 12/09/2005 10:54:26 AM Content-Type: multipart/alternative; boundary="=_alternative 00572150852570D2_=" X-archive-position: 29060 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: brian.x.wisniewski@jpmchase.com Precedence: normal Reply-To: brian.x.wisniewski@jpmchase.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Not scanned: please contact your Internet E-Mail Service Provider for details X-MailScanner-From: oracle-l-bounce@freelists.org --=_alternative 00572150852570D2_= Content-Type: text/plain; charset="US-ASCII" 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 Sent by: oracle-l-bounce@freelists.org 12/07/2005 01:19 PM Please respond to MKanugo To: "'oracle-l@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 --=_alternative 00572150852570D2_= Content-Type: text/html; charset="US-ASCII"
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@ibasis.net>
Sent by: oracle-l-bounce@freelists.org

12/07/2005 01:19 PM
Please respond to MKanugo

       
        To:        "'oracle-l@freelists.org'" <oracle-l@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
 
 
--=_alternative 00572150852570D2_=-- -- http://www.freelists.org/webpage/oracle-l