error in loading of data
Deepak Gupta, September 22, 2003 - 7:56 am UTC
Dear Mr. Tom,
I am using following steps to load data through sqlldr
But I am getting following error
Record 1: Rejected - Error on table MEDI_TEMP1, column MEDI_OPEN.
ORA-01722: invalid number
I tried different options like
instead of "char" I put decimal external / integer external and
field terminated by '$' option
( only those record got inserted without decimal value )
control.file
+++++++++
load data
infile 'medi_new1.txt'
INSERT into table
medi_temp
(
emp POSITION(1:4) char,
medi_open POSITION(17:22) CHAR,
medi_amt POSITION(33:38) CHAR)
medi_new1.txt
+++++++++
C008 $ 32191.2 $ 29000
C030 $ 310 $ 5810
C031 $ 23172.3 $ 6260
C035 $ 1641 $ 6130
C040 $ 174.5 $ 3875
C049 $ 0 $ 8460
C051 $ 495.52 $ 4170
C062 $ 0 $ 11565
C065 $ 0 $ 11070
Thanks + Regards
September 22, 2003 - 8:22 am UTC
looks like you have tabs in there.
when I cut and paste it -- pos(17:22)
C008 $ 32191.2 $ 29000
123456789012345678901234567890
^^^^^^
doesn't line up. check your input data.
what I do to test -- would be to create a temp table "T" with the same number of columns but make all of the datatypes varchar2(4000). then load. then you can "see" what data is loaded into which columns and figure out where you went wrong.
How to load data from tab input fiel
Deepak Gupta, September 23, 2003 - 7:50 am UTC
Dear Mr. Tom,
Hi,
You rightly said , there is tab in my data file, but how to handle it ?
I tried as per your advice but data is not coming in any logical order in the table columns
++++++++++++++++++++
SQL> desc t3
Name Null? Type
------------------------------- -------- ----
EMP VARCHAR2(5)
MEDI_OPEN VARCHAR2(4000)
MEDI_AMT VARCHAR2(4000)
SQL> set ARRAYSIZE 1
SQL> select * from t3;
EMP
-----
MEDI_OPEN
---------------------------------------------
MEDI_AMT
---------------------------------------------
C008
32191.2
$ 29000
C030
310 $ 5
810
C031
23172.3
$ 6260
C035
1641 $
6130
++++++++++
Thanks + Regards
September 23, 2003 - 7:54 am UTC
if you use delimited by whitespace instead of POS()
then:
C008 $ 32191.2 $ 29000
will map to 5 fields -- you can "filler" the two dollar signs out
Tab delimited - terminated by X'9'
Paul, May 06, 2005 - 6:15 am UTC
Tom - nobody thanked you for your original answer on this thread, so I'm doing that now. This was exactly what I needed today.