SQL*loader thinks load zero length varchars are NULLS

[Quoted] [Quoted] I'm trying to load fields into a database using SQL*Loader, and I keep running into this problem:

[Quoted] This data came from an old informix database that is being migrated to oracle. Under informix it was possible to have a VARCHAR field that is not NULL in the database sense, yet has a zero-length string as its value.

But now under Oracle when we try to bring this data in from the ascii dumps with SQL*loader, it complains on all the null length varchar fields that are defined as NOT NULL in the table. Does anyone know how to make SQL*Loader treat zero-length strings as zero-length strings rather than assuming they are database NULL values?

For example using '|' as the delimiter:

trying to load this file:

a-field1|||a-field4|
b-field1|||b-field4|
c-field1|||c-field4|

Into this simple table:
create table (

    f1   varchar   not null,
    f2   varchar   not null,
    f3   varchar   not null,
    f4   varchar   not null,

(In the above example, f2 and f3 should be coming in as zero-length strings, not nulls.)

Is there some way to make this happen easily without having to write my own program to clean up after SQL*Loader?

Allowing f2 and f3 to be nullable means having to go in to a lot of programs and change the code because no null indicator variable processing is currently being done. Received on Wed Jan 30 2002 - 07:33:50 CET