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

Hi

It's not a Sql*loader issue. Oracle database itself considers an empty string ('') a NULL value.

In other words: '' IS NULL => TRUE

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.
Received on Wed Jan 30 2002 - 17:02:41 CET