ORA-01722: invalid number[sqlldr]

Posted on by kishan1 Minute Read

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.

Published by kishan

Desire and obsessive to learn ! Generic technology enthusiast who have dynamic experience in database and other technologies. I have persistence to learn any niche skills faster. Served multiple DBA roles in fortune 500 companies to proactively prevent unexpected failure events. Inclined to take risks,face challenging situations and embrace fear! ***I would like to share my thoughts and ideas to this world !***

Leave a Reply