Re: SQL*Loader questions

In article <832r7u$3h1$1_at_nnrp1.deja.com>,   ccyr6798_at_my-deja.com wrote:
> In article <385487BA.C71B459_at_citec.com.au>,
> Jerome Chik <jerome.chik_at_citec.com.au> wrote:
> > Hi,
> >
> > My datafiles are delimited, e.g. by |
> >
> > (1) How do I make a field of four blank spaces, enclosed by
> delimiters,
> > to be loaded as a NULL instead of four spaces?
> >
> > (2) How do I make the last field, if it starts with a delimiter and
> does
> > not contain anything but a carriage return, to load a NULL instead
of
> a
> > carriage return? (I believe this was what happened). e.g., the
> > following has four columns, the last one is supposed to be a null
> > column, i.e. after the last delimiter:
> >
> > first field|second field|third field|
> > first field|second field|third field|
> >
> > Thanks in advance,
> >
> > Jerome.
> >
> >
>
> I loaded stuff like that "as is", then did an update set field4 =
rtrim
> (field4);
>

There are more direct ways. NULL is easy because of the NULLIF keyword. Here's an example:

      field4 CHAR(4) TERMINATED BY '|' NULLIF field4 = ' '

Note there are also ways to include SQL functions and operators in this part of the loader field clause. Look in your Oracle Server Utilities manuals for the SQL Loader.

HTH --
Ed Prochak
Magic Interface, Ltd.
ORACLE Development, conversions, training and support 440-498-3700 magic_at_interfacefamily.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 13 1999 - 11:50:48 CST