Re: Load data file decimal problem

Try the sqlldr column definition FLOAT EXTERNAL.

Tim

"homerpicard" <homerpicard_at_yahoo.co.uk> wrote in message news: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 Sat Jan 25 2003 - 20:16:29 CET