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 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 ..
First i checked what the error meant :
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 !!!