Re: VARCHAR2: NULL value vs. empty string

Hello Daniel,

Frank is actually right that Oracle is _not_ SQL'92 or SQL'99 compliant with respect to empty strings treatment.

1. In SQL'92 an empty string is _not_ null. Oracle have neglected to fix this problem for close to eleven years.
2. Oracle themselves admit their non-compliance (which is made even more confusing by CLOBs compliance):

<quote>
Note:
IS NULL Semantic Discrepancy

In the SQL 92 standard, a character string of length zero is distinct from a null string.

For an initialized LOB of length 0, you should expect `IS NULL' to return zero (FALSE), since it is the correct and standard compliant behavior. In contrast, a VARCHAR2 of length 0 returns TRUE on 'IS NULL`. </quote>
http://www.engin.umich.edu/caen/wls/software/oracle/appdev.901/a88879/adl07m 13.htm#126418

3. Whilst undoubtedly Mr. Kyte's book is great, it does say much about empty strings vs. nulls on page 38.
Besides, the standard is a more authoritative source than the book.

Rgds.

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1071249923.405427_at_yasure...
> Frank wrote:
>
> > Keith Sauvant wrote:
> >
> >> Hi Group,
> >>
> >> is there _any_ way (Server Parameter?) to make Oracle store empty
> >> strings in Varchar2s without converting them to NULL?
> >>
> >> Thanks in advance
> >> Keith Sauvant
> >>
> >
> > No. Oracle != MS SQL Server (And not ANSI compliant in this matter)
>
> This has nothing to do with ANSI compliance. Where did you ever get the
> idea that it did?
>
> I'd suggest you read page 38 of Tom Kyte's "Expert one-on-one Oracle."
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Fri Dec 12 2003 - 14:16:25 CST