Re: SQL*loader thinks load zero length varchars are NULLS

Steve Mading <madings_at_baladi.bmrb.wisc.edu> wrote in message news:<a3844e$iv0$1_at_news.doit.wisc.edu>...
> I'm trying to load fields into a database using SQL*Loader,
> and I keep running into this problem:
>
> 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.

In Oracle, whether you like it or not, there is no difference between NULL and zero length strings. This behavior might have changed in 9i

Sorry

Sybrand Bakker
Senior Oracle DBA Received on Wed Jan 30 2002 - 15:30:40 CET