Search code examples
shellcsvunixloadersql-loader

sqlldr ORA-01722: invalid number because of decimal number in a csv column

I am trying to load data from a csv file into an orcale table. I am using the sqlldr with an control file Everything works fine but in some cases the row doesnt get loaded because of an decimal number. So in oracle Table the column is : Number(10) - this shouldnt be the problem and my control file looks like this: (their are about 15 more columns but basically its about column quantity_1

OPTIONS (SKIP=1)
LOAD DATA 
 INFILE *
 APPEND
 INTO TABLE ..
 FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"' 
(
 Quantity_1, Quantity_2, Quantity_3,
)

In the csv file the rows for quantity_1 are like

2.58
4343
232
1212

and for the first row he gives the error:

ORA-01722: invalid number

Can anybody help with this??

Solution

  • With your column defined as number(10), it should have rounded to 3 on insert. I suspect your real data is larger that 2.58. Anyway your column should be defined as number(12,2), that is, a total of 12 digits long, 2 of those to the left of the decimal point. i.e. 9999999999.99