Re: blank fields in Oracle

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
>
Received on Thu Mar 13 2003 - 13:17:09 CST