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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s