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

On Sun, 14 Dec 2003 10:20:49 -0800, Daniel Morgan <damorgan_at_x.washington.edu> wrote:

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

 Which is what everyone has been agreed on from the start.

>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);

 That's a cop-out, and you know it. Oracle's native varying-length character datatype, VARCHAR2, is non-compliant with regards to empty string storage, effectively as a result of its implementation and storage. Whether this is much of a problem is another debate entirely, since distinguishing empty string from NULL would require at least one more bit of storage somewhere. But that's not _this_ debate.

 Trying to argue that ANYDATA is a compliant character datatype is specious; it's an ADT that can itself be null, or can hold one of the Oracle datatypes. It still can't store an empty string, since an empty string is a NULL VARCHAR2. All you've done is put an extra layer of indirection in, hiding it inside another type.

 And try building an index on it. Or using any of the ANSI string functions on it. ANYDATA is not a character datatype, it's a container object for other datatypes.

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

 Presumably you're not being serious? All you now have proof of is that you have a container that is storing a VARCHAR2.

INSERT INTO t
VALUES (sys.anyData.convertVarchar2(NULL));

 Now distinguish the two rows from each other. Which is back to the original issue.

-- 
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)