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.

ORA-01722: invalid number

I already covered this error in a different situation but let's look at it from an entirely different point of view.

create table test_table (col_A varchar2(10))
/
insert into test_table values('A')
/
insert into test_table values('1')
/
commit
/
select * from test_table
where col_a = 1;

ORA-01722: invalid number

This error occurs because there is an implicit string to number conversion going on here.
As you see i omitted the tick marks around the value 1 in the query, so Oracle is trying to convert the column value from varchar2 into number and then perform the comparison.
As soon as oracle hits against the 'A' value, the conversion fails and the error is raised.

One could think that this kind of errors are of scarce importance because they can be easily spotted, but actually these kind of errors can easily turn themselves into big troubles.

Why?

I was at a site where they created a column of type varchar2 and they kept only numeric codes in it for years.

You can see what happens by removing the record containing the 'A'

delete from test_table where col_a='A'
/

select * from test_table
where col_a = 1;

col_a
1

No errors this time!

One day the people of this company decided to add some alphanumeric codes and the first time they ran a procedure reading data from that table, it blew up.

How can it be, a program that has been running for years!

Well, it's easy, a very trivial error can cause a big mess *years* after it was deployed to production, because it was just working by pure chance.

So, be careful when storing numbers into varchar2 columns, if you don't write your queries properly they are going to transform themselves into ticking bombs, silently awaiting their turn to explode.

See message translations for ORA-01722 and search additional resources

---

ORA-01722: numero non valido
ORA-01722: número no válido
ORA-01722: número no vàlid
ORA-01722: Nombre non valide
ORA-01722: Ungültige Zahl
ORA-01722: μη αποδεκτός αριθμός
ORA-01722: ugyldigt tal
ORA-01722: ogiltigt tal
ORA-01722: ugyldig tall
ORA-01722: virheellinen numero
ORA-01722: nem megengedett szám
ORA-01722: număr eronat
ORA-01722: Ongeldig getal.
ORA-01722: número inválido
ORA-01722: número inválido
ORA-01722: неверное число
ORA-01722: neplatné číslo
ORA-01722: neplatné číslo
ORA-01722: niepoprawna liczba
ORA-01722: geçersiz sayı

Avoiding ORA-01722 in Apex tabular forms

Have you ever experienced that uncomfortable feeling as it happens when you find out you've spent the last three hours in reinventing the wheel?

Well, luckily enough, it didn't happen to me today ;-)

I had a page in an Oracle Application Express application, based on a tabular form.
Tabular forms are a powerful tool, but when it comes to validating the content of each cell, they can quickly turn into a programmer's nightmare.

For instance, how to prevent that a user enters invalid data and is taken to the unhandled pl/sql exception page where it will be notified of some friendly error like the following?

Error in mru internal routine:
ORA-20001: Error in MRU: row= 1,
ORA-01722: invalid number, update schema.table set ...

This error most likely occurs because the user entered either an invalid decimal point or an invalid thousands separator in a numeric column.

So, being in such a situation, my first thought was to turn all my numeric columns into varchar2 and then handle the problem line by line, checking if the numeric format was acceptable or not.

But there was a sort of voice in the background asking me: "is this mess really needed? aren't there any other viable options?"

I was almost sure that there was something in Apex allowing me to specify a numeric format model even for tabular forms.

And there is indeed.

Open up the tabular form report and click on the edit icon of the column.
The attribute i was looking for is the first in the "Column Formatting" block and is named "Number/Date Format".
I didn't pick the format model from the list, but entered a new one: 999G999G990D99.

So, now, a user is free to enter numbers as plain digits with a decimal separator or formatted numbers including the thousands separators, without worrying about ORA-01722.

A small fix for a developer but a huge leap for user friendliness!