ORA-01722: invalid number[sqlldr]

When loading data using sqlloader from a csv file to a table,due to improper format of integers in specific columns, we get ora-1722 error

"Record 2: Rejected - Error  on table sales, column Total_Profit.
ORA-01722: invalid number "

Solution:

Add filler option on the column where you have format mismatch of numbers

load data
infile '/home/oracle/bigtable.csv'
badfile '/home/oracle/bigtable.bad'
discardfile '/home/oracle/bigtable.dsc'
into table sales
fields terminated by ","
(Country,Item_Type,Sales_Channel,Order_Priority,Order_Date,Order_ID,Ship_Date,Units_Sold,Unit_Price,Unit_Cost,Total_Revenue,Total_Cost,Total_Profit FILLER)

The error is bypassed!

Commit point reached - logical record count 1048576

Table SALES:
  1048576 Rows successfully loaded.

Check the log file:
  sale.log
for more information about the load.

Leave a Reply