SQL*Loader, ORA-01722 and the importance of being blank

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Although I wrote hundreds of SQL*Loader control files over the years, yesterday i came across one of those trivial situations that takes hours to be sorted out because of some tiny detail.

The task: to load a text file containing fixed-length fields and numeric fields are formatted using a postfix notation, minus sign for negative numbers or a blank for positive numbers.

Piece of cake i thought, i can format the field using an inline SQL function like "TO_NUMBER(:COL2, '999999MI')" as in:

load data
infile *
truncate
into table test_table
(
COL1        POSITION(01:19)    CHAR,
COL2        POSITION(20:26)    CHAR "TO_NUMBER(:COL2,'999999MI')",
COL3        POSITION(27:27)    CHAR
)
BEGINDATA
THIS ROW IS OK     123456-Y
THIS ROW IS NOT OK!234567 Y
THIS ROW IS NOT OK!012345 Y

And here is what i found in the SQL*Loader log file:

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 18 23:21:39 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   test.ctl
Data File:      test.ctl
Bad File:       test.bad
Discard File:   none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TEST_TABLE, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

Column Name                     Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1                                 1:19    19           CHARACTER    
COL2                                20:26     7           CHARACTER    
SQL string for column : "TO_NUMBER(:COL2,'999999MI')"
COL3                                27:27     1           CHARACTER    

Record 2: Rejected - Error on table TEST_TABLE, column COL2.
ORA-01722: invalid number

Record 3: Rejected - Error on table TEST_TABLE, column COL2.
ORA-01722: invalid number


Table TEST_TABLE:
1 Row successfully loaded.
2 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.


Space allocated for bind array:                   2304 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         2
Total logical records discarded:        0

Run began on Mon Aug 18 23:21:39 2008
Run ended on Mon Aug 18 23:21:40 2008

Elapsed time was:     00:00:00.54
CPU time was:         00:00:00.12

The corresponding rows were also recorded in the badfile.

I must admit that this problem has tortured me for a couple of hours, before i finally got the "inspiration".

Eventually i realized that i had a problem with the blanks when i managed to re-create the error condition using a simple query. In particular i noticed that one can perform the following SELECT without problems:

select to_number('000000 ','999999MI') t from dual;

T
-
0

but one gets ORA-01722 when the trailing blank is trimmed:

select to_number('000000','999999MI') t from dual;

ORA-01722: invalid number

The main problem is in the fact that SQL*Loader by default ignores blanks or better said it strips them out altogether. Blank trimming is somewhat documented in the SQL*Loader Reference manual, but it's easy to overlook such fundamental information.
At this point i had a strong suspect on the fact that SQL*Loader was removing the trailing blank, regardless of the fixed-length specification, so i decided to enable the PRESERVE BLANKS option at the file level:

load data
infile *
truncate
preserve blanks
into table test_table
(
COL1        POSITION(01:19)    CHAR,
COL2        POSITION(20:26)    CHAR "TO_NUMBER(:COL2,'999999MI')",
COL3        POSITION(27:27)    CHAR
)
BEGINDATA
THIS ROW IS OK     123456-Y
THIS ROW IS NOW OK 234567+Y
THIS ROW IS NOW OK 012345 Y

And this time it worked without a hitch.

Well, almost, first i had to sort out a side-effect of PRESERVE BLANKS, because in my real-life scenario i had also a trailing numeric column that started complaining about the presence of blanks.
So i modified the field definition to include NULLIF field = BLANKS and after doing that my control file was finally ok.

Edited on Tuesday 19:
note also that the MI numerical format allows to specify either a blank or a plus (+) sign indifferently.
I inserted a plus sign in line 2 of the last datafile version to clarify this point.