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

From: Daniel Gustafsson <daniel_at_mimer.se>
Date: 15 Dec 2003 06:53:44 -0800
Message-ID: <de4cfd03.0312150653.6cdd0347@posting.google.com>

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1071425957.927085_at_yasure>...
>
> The standard consists of three separate compliance levels. All major
> commercial RDBMS products are level 1 compliant.

There are no compliance levels in the current SQL standard.

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

This is a proof that you are not serious. An instructor should not display such an astonishing ignorance.

(As an aside, this has nothing to do with Microsoft.)

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

You must be joking? Otherwise it is another proof that you are confused.

Andy Hassal posted a good example, I can repeat it again:

SELECT CASE

       WHEN '' IS NOT NULL THEN 'Core SQL-99 Compliant'
       WHEN '' IS NULL     THEN 'Not compliant'
       END

   FROM T; (this statement is, by the way, a valid SQL-92 Intermediate Level statement)

Regards
Daniel Gustafsson
Mimer SQL Development
http://www.mimer.se/ Received on Mon Dec 15 2003 - 08:53:44 CST