Home -> Usenet -> comp.databases.oracle -> Load data file decimal problem

Load data file decimal problem

From: homerpicard <homerpicard_at_yahoo.co.uk>
Date: 24 Jan 2003 11:12:19 -0800
Message-ID: <51b753a6.0301241112.4d62874e_at_posting.google.com> 






I have a table t1 defined as


Field     Type
-----     -----




field1    varchar2(7)


field2    number(10,2)


field3    number(10,2)




I also have a data file 'data.dat':



xxxxxx,xxxxxxx,99999.99,9999999


xxxxx,xxxxx,99999999,99999


xxxxxxxx,xxxxxxxxxx,99999,9999999.99



i.e. some of the decimal values have decimal places after them while
others don't. I'm trying to use the following command to load data
from the file into the table:



load data


infile 'data.dat'


truncate into table t1


fields terminated by ','


(col1 filler                    char,



 field1                         char,
 field2                         "to_number(:field2,'999999999.99')",
 field3                         "to_number(:field3,'999999999.99')")





Unfortunately I get the error: 'ORA-01722: invalid number'



I have also tried to vary the load the command:



load data


infile 'data.dat'


truncate into table t1


fields terminated by ','


(col1 filler                    char,



 field1                         char,
 field2                         decimal(10),
 field3                         decimal(10))





this time getting the error: 'Invalid zoned decimal nibble'



(incidentally I've used various sizes for the decimal field from 7 -


50 all with the same result!) Does anyone have a clue as the source of
my problems(s) ?


Many thanks 



Homer
Received on Fri Jan 24 2003 - 20:12:19 CET












Original text of this message