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 !

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

  1. Vance Duncan

    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.

  2. Laurent Schneider Post author

    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

  3. Pingback: Fix Oracle Database Error 1722 Ora-01722 Invalid Number Windows XP, Vista, 7, 8 [Solved]

Comments are closed.