Re: VARCHAR2: NULL value vs. empty string - Proof Oracle Supports Zero Length Strings

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 14 Dec 2003 10:20:49 -0800
Message-ID: <1071425957.927085@yasure>

Frank wrote:

> Daniel Morgan wrote:
>

>> 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."
>>

>
> Oracle is not MS SQL server (and [Oracle] is not ANSI compliant in
> this matter).
> The ANSI standard defines an empty string as defined (which is not
> the same as undefined, or NULL). Oracle still regards an empty string
> as undefined.
>
> Indeed, the OP problem has nothing to do with ANSI compliance; I just
> noted that Oracle wasn't ANSI compliant in this matter, which kind of
> surprised me too, when I found out.
>
> Page 38/39 are about NULLs and NULL comparison - not about empty strings,
> whatever gave you that idea?
>
> And VC has given enough links to prove otherwise, if not, check your
> own; the first link refers to a table, composed based on
> an article, which states:
> <quote>
> For E131: Oracle won't distinguish NULLs from blank strings.
> </quote>
> It's in the Core Feature section of the article: where it says
> <quote>
> This section shows the exceptional cases where at least one DBMS misses
> a core SQL:1999 feature.
> </quote>
>
> Based on this, I'm even inclined to say, that the first link quotes it's
> source wrongly.
>
> Your second link does not involve strings, either. As for your
> conclusion, that '' does indeed equal '',
> try this - this is 9.2.0.4 on Linux:
>
> SQL> select nvl('','Is Null') from dual;
>
> NVL('',
> -------
> Is Null
>
> SQL> select nvl(NULL,'Is Null') from dual;
>
> NVL(NUL
> -------
> Is Null
>
> SQL> select 1 from dual where '' IS NULL;
>
> 1
> ----------
> 1
>
> SQL> select 1 from dual where '' = NULL;
>
> no rows selected
>
> Oracle regards '' as NULL. Even your conclusion is easily proven
> wrong:
> SQL> select 1 from dual where '' = '' ;
>
> no rows selected
>
> '' is NOT equal ''
>
> QED, rmc, eod

First of all there is no such thing as an ANSI standard if one defines "an" as meaning a singular entity. The ANSI standard is not one thing. The standard consists of three separate compliance levels. All major commercial RDBMS products are level 1 compliant. What you folks have been posting is pure unadulterated nonsense as you have likely never actually read the standard and are make assumptions based on marketing hyperbole and an assuming that if it isn't done the Microsoft way it most be wrong. The truth is that SQL Server is NOT ANSI compliant on default installation. It achieves compliance ONLY by modifying its defaults.

In the ANSI standard NULL is defined as the lack of a value ... not as a   zero length string and this is not subject to debate. It is the definition.

Does Oracle support zero length strings? Absolutely. That you don't know it is evidence that you have not been keeping up with Oracle. As you know in version 9i Oracle added full support for ANSI joins. It also added support for zero length strings. And here's the proof.

• here's the table CREATE TABLE t (x sys.anyData);
• here's the insert statement INSERT INTO t VALUES (sys.anyData.convertVarchar2(''));

COL typeName FORMAT a20

• proof the row was stored SELECT COUNT(*) FROM t;
• proof the data type is VARCHAR2 SELECT t.x.gettypeName() typeName FROM t t;

Now you have the proof.

If you want to retrieve that empty string you will need to either go to http://tahiti.oracle.com and learn how to use the anyData data type or take my class at the University of Washington. I have two open seats for the Winter Quarter starting in January ;-)

-- 
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 - 12:20:49 CST