Your manager asked you to load an Excel file in a table. Ok, you look at the header, create a basic table with meaningfull datatype, you open Excel and save as csv, you load your data. But then you get ORA-1722. What happened ?
Ok, let’s do it
create table t(x number not null, y number, z number not null);
LOAD DATA INFILE * INTO TABLE T TRUNCATE FIELDS TERMINATED BY ';' (X, Y, Z) BEGINDATA 1;1;1 2; ;2 3;3;3
$ sqlldr scott/tiger control=foo.ctl $ vi foo.log ... Record 2: Rejected - Error on table T, column Y. ORA-01722: invalid number
Here it is pretty eye-popping, but you probably have 10 years of market data to load with hundreds of columns and most of the columns are empty or/and obsolete.
The thing is, Excel did put a space for your “number” datatype, space is not a valid number !
SQL> select to_number(' ') from dual; select to_number(' ') from dual * ERROR at line 1: ORA-01722: invalid number
A workaround is for each nullable numeric column to specify nullif column=blank
LOAD DATA INFILE * INTO TABLE T TRUNCATE FIELDS TERMINATED BY ';' (X, Y NULLIF Y=BLANKS, Z) BEGINDATA 1;1;1 2; ;2 3;3;3
$ sqlldr scott/tiger control=foo.ctl $ vi foo.log ... Table T: 3 Rows successfully loaded. 0 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 hope I will remember this next time I am sqlloading from Excel into Oracle !Google+