By Laurent Schneider, Oracle Certified Master
Assume that your manager has asked you to load an Excel
file in a table. OK, you look at the header, create a basic
table with meaningful datatype, you open Excel and save as
csv, you can load your data as comma-delimited.
But then you get ORA-1722. What happened -
Ok, let's see why sqlldr throws an ORA-01722 error on a
NULL field:
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 for the ORA-01722 error 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 !
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
