SQL*Loader Date error (ORA-01858)

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

SQL*Loader Date error (ORA-01858)

SQL*Loader Date error (ORA-01858)

2005-12-09 - By brian.x.wisniewski@(protected)

Reply: 1 2 3 4 5

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@(protected)>
Sent by: oracle-l-bounce@(protected)
12/07/2005 01:19 PM
Please respond to MKanugo

To: "'oracle-l@(protected)'" <oracle-l@(protected)>
cc:
Subject: SQL*Loader Date error (ORA-01858 (See ORA-01858.ora-code.com))

Hello All,

I'm trying to load data using SQL Loader and ran into the ORA-01858 (See ORA-01858.ora-code.com): 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 (See ORA-01858.ora-code.com).

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

 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
 
 
 
 
 
<table width=100%>
<tr valign=top>
<td>
<td>Madhavi Kanugo <MKanugo@(protected)>

 Sent by: oracle-l-bounce@(protected)
12/07/2005 01:19 PM
 Please respond to MKanugo
<td>        
         To:
       "'oracle-l@(protected)'"
<oracle-l@(protected)>
         cc:
       
         Subject:
       SQL*Loader Date error (ORA-01858 (See ORA-01858.ora-code.com))</table>
 
 
 Hello  All,
  
 I'm trying to load data using SQL Loader
and ran into the ORA-01858 (See ORA-01858.ora-code.com): 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 (See ORA-01858.ora-code.com). 
  
 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