Re: SQL*Loader Date error (ORA-01858)
- From: brian.x.wisniewski@xxxxxxxxxxxx
- To: MKanugo@xxxxxxxxxx
- Date: Fri, 9 Dec 2005 10:51:42 -0500
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:
