Re: problem with SQL*Loader

Amy Leone wrote:
>
> On Feb 17, 1996 00:00:00 in article <Re: problem with SQL*Loader>, 'Charles
> Wolfe <cwolfe_at_ix.netcom.com>' wrote:
>
>
> >Gene Gurevich <geneg_at_umich.edu> wrote:
> >> Hi all! I am using SQL*Loader 7.1.4 to load the data into ORACLE
> >>tables. It seems to me that SQL*Loader translates blanks into nulls while
 

> >>loading data... Has any one else experienced this? How can I prevent it?
> >>TIA
> >
> >In order to load all-blank CHAR fields as blanks those fields must be
> >enclosed within delimiters. Otherwise such fields are loaded as null.
> >Blank DATE and numeric fields cause the record to be rejected. In order
> >to load those fields as null it is necessary to use the NULLIF BLANKS
> >clause.
> >
> >You should be cautious about storing blank fields as blanks, as
> >this generates the same amount of storage overhead as if those fields
> >contained any other alphanumeric characters. Also, you shouldn't use
> >blanks to indicate (or test for) absence of value in a field. That's
> >what nulls are for.
> >
> >regards,
> >Chuck
>
> The experience I had is that Oracle makes it very difficult for you to
> store blanks, i.e. even if you manage to store a blank, it will be
> retrieved as a NULL. So there isn't much point in trying to prevent it.
> It's preferable to (and I realize it's probably too late now) design your
> database in a way that minimizes blanks/nulls. Otherwise you may end up
> messing with indicator variables, which are a problem in themselves. Just
> my .02.
>
> Amy

You can try too things:
1. Specify field by position rather then terminators. 2. Use the default value clause to enter blanks instead on nulls.

The important thing to understand is that oracle truncates blank from end of field for varchar2 columns by default. An easy solution could be creating CHAR columns.

                Hadar Paiss Received on Sat Feb 24 1996 - 00:00:00 CET