SQL*Loader -- Loading null numerics ...

[CraigJConrad]

Hello to all!

I need to load data into Oracle 8 from an input file that is fixed-length. One of the fields is ZONED (what Cobol would identify as SIGNED). When this field is to be loaded as NULL, then the first byte of its input field has X'FF' in it. When it is not null, then this byte is part of the actual numeric value to load.

I was hoping that I could check for the X'FF' in the loader -- if found, load NULL, if not found, load the value. However, I believe that the field will be validated BEFORE the null is checked. This would lead to a load error, and the record would be rejected.

What I was hoping for:
AL_PER_ADDR_NO POSITION (0154:0163) ZONED(10)
NullIf (0154:0154) = X'FF'

Is there any creative way around that? It is not an (easy) option to dedicate a separate byte to the null indicator (there are actually hundreds of these fields in the total load process), so that is a last alternative.

All help appreciated!

Craig

 

[hoinz]

Hello,

not very elegant, but might work:
run SQL*loader with two WHEN-clauses:

WHEN (154) = X'FF' load column as NULL
WHEN (154) != X'FF' load column as ZONED

hope this helps

 

[CraigJConrad]

Hoinz,

Thanks for the response! To play with this, I downloaded Oracle 10 to my PC and tried from there. After some time, I went back to try the original:

AL_PER_ADDR_NO POSITION (0154:0163) ZONED(10)
NullIf (0154:0154) = X'FF'

And ... it worked! I had been told that this couldn't be done with zoned fields, but apparantly it can. I should have tried the obvious from the beginning. Now, I hope that it works on Oracle 8 just as well ...

Thanks again!

Craig