Re: blank fields in Oracle

Ray Porter wrote:

> Hi Daniel,
> BTW, thanks for the call this morning. Our DBAs are generally recommending
> varchar2 for everything except single byte fields and that's what we have so
> far. The recommendation you made during our conversation this morning that
> I'm coming to believe would make the greatest difference (a change that
> represents and about face for me personally), is probably going to be hard
> or impossible to implement until we move production stuff to a new machine.
> Even then it'll be a hard sale for some developers and customers. I'm
> becoming convinced that a big part of our problem is competition for machine
> resources and your suggestion offers the best chance of reducing or
> eliminating that bottleneck.
>
> The data in this application is refreshed nightly from mainframe extracts.
> Currently, we are specifying PRESERVE BLANKS in the sql loader control files
> because we have some fields (mostly codes and flags) where a blank value is
> legitimate and has meaning. Preserving blanks does make string comparisons
> difficult so what we'd really like to have happen, during the load, trim
> trailing blanks from most fields but preserve at least one blank in every
> character field that does not allow nulls if that field in the extract file
> contains only blanks. We are only allowing null values in date fields where
> we legitimately have no known value (i.e., death date where the alumnus is
> still alive as far as we know).
>
> Thanks for your help,
> Ray Porter
>
> "DA Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3E70BB32.F2DC3E2D_at_exesolutions.com...
> > Ray Porter wrote:
> >
> > > Thanks for the reply. So you're saying that if we use CHAR, Oracle will
> > > preserve a blank in the field, even if we do not specify PRESERVE BLANKS
> in
> > > the control file? If so, that's actually the behavior we want. It's
> okay
> > > if Oracle treats a trimmed blank as a null. My problem is that our
> Database
> > > Management folks are specifying that all character fields, except those
> with
> > > a length of 1, have to be varchar2. If I understand what you're saying,
> we
> > > actually need CHAR for all our elements except a few that need greater
> than
> > > 2000 byte length. Um, I need to convince our db person to allow me to
> test
> > > this. That way I might be able to make a case for an exception to the
> rule.
> > >
> > > Thanks,
> > > Ray Porter
> > >
> > > "FC" <flavio_at_tin.it> wrote in message
> > > news:Sg0ca.73179$zo2.1898550_at_news2.tin.it...
> > > >
> > > > Did you try using the CHAR datatype?
> > > > If you use CHAR, the minimum length is 1, max is 2000 (at least in
> version
> > > > 8.1.7), if you insert a zero length string, Oracle will pad it with a
> > > blank
> > > > automatically.
> > > > Oracle treats zero length strings as nulls, you can easily test this
> > > > situation executing the following sql:
> > > >
> > > > SELECT NVL2(TRIM(' '), 'NOT NULL', 'NULL') FROM DUAL;
> > > >
> > > > result: 'NULL'
> > > >
> > > > SELECT NVL2(TRIM('X'), 'NOT NULL', 'NULL') FROM DUAL;
> > > >
> > > > result: 'NOT NULL'
> > > >
> > > >
> > > > Bye,
> > > > Flavio
> > > >
> > > >
> >
> > Rule of thumb is to never use CHAR in Oracle. VARCHAR2 is both more
> efficient
> > for storage and because of the trailing blanks issue ... makes string
> > comparison's problematic.
> >
> > If moving from CHAR to VARCHAR2 the spaces are not removed. The spaces are
> just
> > not right padded with new inserts/updates. You can do the following to
> remove
> > leading and trailing spaces.
> >
> > UPDATE <table>
> > SET <column_name> = TRIM(<column_name>);
> >
> > Daniel Morgan
> >

You can enbed functions into SQL*Load control scripts. I'd look at, where possible, embedding something like DECODE OR NVL(TRIM to convert those spaces into a default value. It will not only solve some problems but potentially avoid others.

Here's a simple example of a function in a control script:

LOAD DATA
INFILE *
INSERT
INTO TABLE decodemo
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(
 fld1,
 fld2 "DECODE(:fld1, 'hello', 'goodbye', :fld1)" )
BEGINDATA
hello,""
goodbye,""
this is a test,""
hello,""

Daniel Morgan Received on Thu Mar 13 2003 - 16:43:25 CST