Categories
dba

ORA-01722: invalid number and sql loader

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 !

By Laurent Schneider

Oracle Certified Master

3 replies on “ORA-01722: invalid number and sql loader”

This does not seem to work when specifying tab-delimited. I have:

OPTIONS (SKIP = 1)
LOAD DATA
REPLACE
INTO TABLE ING_TSRM_HISTORICAL_SWAP_STAGE
FIELDS TERMINATED BY ‘\t’
OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(
ROW_ID RECNUM,
USI_NAMESPACE “trim(:USI_NAMESPACE)”,
USI_VALUE “trim(:USI_VALUE)”,
ASSET_CLASS “trim(:ASSET_CLASS)”,
PRODUCT_TYPE “trim(:PRODUCT_TYPE)”,
UPDATED_ON_DATETIME “trim(:UPDATED_ON_DATETIME)”,
TRADE_PARTY_1_LEI “trim(:TRADE_PARTY_1_LEI)”,
TRADE_PARTY_2_LEI “trim(:TRADE_PARTY_2_LEI)”,
TRADE_PARTY_1_REF_NUMBER “trim(:TRADE_PARTY_1_REF_NUMBER)”,
NOTIONAL_AMOUNT_PARTY nullif (NOTIONAL_AMOUNT_PARTY=BLANKS),
NOTIONAL_AMOUNT_CPTY nullif (NOTIONAL_AMOUNT_CPTY=BLANKS),
REPORTING_PARTY “trim(:REPORTING_PARTY)”,
TRADE_TYPE_INDICATOR “trim(:TRADE_TYPE_INDICATOR)”,
PROCESS_STATUS constant ‘Staged’
)

It breaks if either NOTIONAL_AMOUNT_PARTY or NOTIONAL_AMOUNT_CPTY are blank. Data was exported from Excel using tab-delimited text format.

This is bug 21040, reported in 5.1.22 and closed Jun 7, 1989 as 92 – Closed, Not a Bug

The workaround is documented in note 414394.1 :
Remove clause OPTIONALLY ENCLOSED BY from control file

Cheers

Leave a Reply

Your email address will not be published.