Tuesday, August 19, 2008

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.

6 comments:

Andre Linssen said...

I am facing the same problem, but your solution doesn't work here. I have a number field (yes, it's defined just as NUMBER, nothing else), and the input has the format of 1542,12 or just 642 for example. The numeric characters are . and , to make things more funny. I don't get it :( nothing works ARgggHHhh!!!! I am getting crazy!

Byte64 said...

Andre, probably you are facing a slightly different problem that looks the same but it isn't.

It would be a good idea to provide the DDL for the table to be loaded, a couple of lines of the data file as well as the source of the control file to start off, otherwise it's like guessing numbers using a crystal ball.

Flavio

Danish said...

Hi,
I'm facing a similar problem where a .dat file gets loaded to Oracle and the ctl file looks like...


into table tmp_archive_tbl
when (1) != '*'
fields terminated by ',' optionally enclosed by '"'
( mailing_id POSITION(01:10) INTEGER EXTERNAL,
profile_id POSITION(12:21) INTEGER EXTERNAL,
created_tm POSITION(23:41) DATE "YYYYMMDDHH24:MI:SS",
source_code POSITION(43:52) CHAR,
content_code POSITION(54:63) CHAR,
action_code POSITION(65:74) CHAR,
-- subaction_code POSITION(76:85) CHAR,
subaction_code POSITION(176:185) CHAR,
tl_module_id POSITION(87:96) INTEGER EXTERNAL,
rule_bit_seq POSITION(98:117) INTEGER EXTERNAL,
load_job_id POSITION(119:128) INTEGER EXTERNAL,
unique_flg POSITION(130:130) INTEGER EXTERNAL,
ts_segment_id POSITION(23:32) CHAR,
wave_id POSITION(164:174) )

When wave_id is a negative number, it gets inserted into the tbl but the log file says that error is on subsequent line. Always the same.

value used for ROWS parameter changed from 1000 to 400
Record 2: Rejected - Error on table TMP_ARCHIVE_TBL, column MAILING_ID.
ORA-01722: invalid number

Record 3: Rejected - Error on table TMP_ARCHIVE_TBL, column MAILING_ID.
ORA-01722: invalid number

The .dat file has ...
36564788 1597888538 2010091714:29:40 RPLY H B 1 0 0 415089132 1 0 pmonitor3 -3550397807 OTHR 6540121 -3550397807
36564788 1597888538 2010091714:29:40 RPLY H B 1 0 0 415089132 0 0 pmonitor3 -3550397807 OTHR 6540121 -3550397807
36564788 1597888538 2010091714:29:41 RPLY H B 1 0 0 415089132 0 0 pmonitor3 -3550397807 OTHR 6540121 -3550397807

It works when wave_id is positive.

Also I am able to insert the '-3550397807' manually in Oracle...

SQL> insert into tmp_archive_tbl (wave_id) values ('-3550397807');

1 row created.

SQL> select wave_id from tmp_archive_tbl where wave_id like '%550%';

WAVE_ID
----------
-3.550E+09

What could I change to the ctl file to make this work? Thanks!

Byte64 said...

I believe this is a bug I've already seen some time ago on Oracle 10gR2.
It was somewhat elusive, the only way to get rid of it was to set BINDSIZE equal to the record length, including the newline character(s). I could accept this because the system was not loading big files.

If setting BINDSIZE to a single record size fixes the problem, I'd suggest to make further experiments with multiples, as the performance of the data load will be adversely affected if processing one line at a time, especially for large data loads.

If this doesn't solve the problem, you might want to send me the DDL for creating the table, the whole control file and tell me the database version you are using.

Flavio

RuthB said...

Thanks for your blog! I had the same error, and though your solution wasn't the one I needed it led me to it. I'd created a csv file from an excel spreadsheet and ftp'd it to the unix server. The last column was a not null number column. All the data was numeric. But the records were rejected on the last column with 'ORA-01722: invalid number'
WHAT?!!

Apparently there were still line returns in the data file though I couldn't see them when I viewed the file in unix. The solution was to run dos2unix on the file.

Anonymous said...

Hi All,

Our requirement is ,

1) we have a default value for a CLOB column (For Ex : '1234')

2) In our Input file, we have some column values that are empty.

For Ex :

Name(varchar) Value(clob)

a1 clob1
a2
a3 clob3

3) Our control file is such that when imported above csv using SQL-LOADER,our data in DB should be as below

Name Value

a1 clob1
a2 1234
a3 clob3

How can we achieve this ?

I read SQL strings are not supported on CLOB.

Is there any SQL-LOADER specific expressions/procedure to achieve this?



Please help on this.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles