SQLLOADER has “ORA-01722: Number not valid ” with CSV File

The problem

While importing a csv file with sql loader i has this error :

Record 1: Rejected - Error on table MY_TABLE, column MY_COLUMN3 .
ORA-01722: Number not valid

Record 2: Rejected - Error on table MY_TABLE, column MY_COLUMN3 .
ORA-01722: Number not valid

Table MY_TABLE:
  1 Row successfully loaded.
  11 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

I spent hours on this problems .

My configuration

My CTL file :

OPTIONS (SKIP=0,ERRORS=1000,SILENT=(FEEDBACK))
LOAD DATA
APPEND
INTO TABLE MY_TABLE
FIELDS TERMINATED by ';'
TRAILING NULLCOLS
(
MY_COLUMN1 DECIMAL EXTERNAL
MY_COLUMN2 DECIMAL EXTERNAL
MY_COLUMN3 DECIMAL EXTERNAL
)

My table :

CREATE TABLE &1.."MY_TABLE"
       (                                                                                 
       ID                           NUMBER GENERATED BY DEFAULT AS IDENTITY,            
          MY_COLUMN1             FLOAT                      NOT NULL ,                 
MY_COLUMN2             FLOAT                      NOT NULL,
MY_COLUMN3             FLOAT                      NOT NULL,                 
    )                                                                                    
    PCTFREE 10                                                                           
    TABLESPACE "MY_TABLESPACE"

My CSV file :

3.62;0.091;11.2;-3.54
0.613;0.181;169.4;-10.18
..

The solution

First i checked what the error meant :
http://www.dba-oracle.com/t_ora_01722_sql_loader_sqlldr.htm

This error can mean there are blanks in the file but after checking the csv file there was none. However I noticed the last column MY_COLUMN3 causing problems had negative values. But according to oracle documentation the type Oracle FLOAT can handle negative values. And the ctl was correctly configured to handle negative values with MY_COLUMN3 DECIMAL EXTERNAL. So what is going on ?

Finally i discovered that after manually modifying the CSV file in the unix server the problem was solved ! So i figure out the problem was not coming from ctl file but from the csv file.

After many trials and errors I found out the file was a dos file

vi /data/myfile.csv
</data/myfile.csv" [noeol][dos] 13L, 832C

Actually this csv file is a file coming from Excel document on Windows and being transferred to the unix server.

So I tried the command dostounix and it fixes my issue !!!

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s