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.
