Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: VARCHAR2: NULL value vs. empty string

Re: VARCHAR2: NULL value vs. empty string

From: Van Messner <messner1_at_NOSPAMbestweb.net>
Date: Sun, 14 Dec 2003 18:47:47 -0500
Message-ID: <vtptmth6i1nm62@corp.supernews.com>


This is from Metalink - Note 1011340.6 (titled What is the Difference Between NULL and a Zero Length String?)

"In Oracle version 7.0, a zero length string is treated by Oracle as NULL. It is documented however that this will change in future releases. Starting in release 7.1.3 and on through 8.1.7, a string of zero length ('') is not equivalent to a NULL.
Your application should use a NULL when the value is unknown.

The following statement appears in the 7.3.x and 8.0.x release notes, as well as in the Getting to Know Oracle8i manual: A string of zero length ('') is not equivalent to a NULL. According to the ANSI SQL 1992 Transitional standard, a zero-lenth or empty string is not the same as NULL. The Oracle server may comply fully with this aspect of the standard in the future, therefore, it is recommended that applications ensure that empty strings values and NULL are not treated equivalently.

And in the migration to 9i docs:

"A string of zero length ('') is not equivalent to a NULL value. According to
the ANSI SQL 1992 Transitional standard, a zero-length or empty string is not
the same as NULL. Ensure that applications do not treat empty strings and NULL
values equivalently."

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1071424889.658804_at_yasure...
> Daniel Gustafsson wrote:
>
> > From the standard compliance tables in the Oracle SQL Reference
> > manual.
> > "Oracle partially supports these subfeatures:
> > E021-02, CHARACTER VARYING data type (Oracle does not distinguish a
> > zero-length VARCHAR string from NULL)
> > E021-03, Character literals (Oracle regards the zero-length literal ''
> > as being null)"
>
> And how, exactly, do you correlate this with the ANSI standard? NULL in
> the standard is a lack of a value ... it is not a zero length string.
>
> You are making an assumption. And an incorrect one.
>
> > Daniel Gustafsson, Mimer SQL Development
> > http://www.mimer.se
>
>
> --
> 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 Sun Dec 14 2003 - 17:47:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US