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: [Q] Porting SQL to Oracle

Re: [Q] Porting SQL to Oracle

From: andrewst <member14183_at_dbforums.com>
Date: Wed, 11 Jun 2003 16:02:11 +0000
Message-ID: <2990247.1055347331@dbforums.com>

Originally posted by Billy Verreynne
> Scott Mattes wrote:
>
> > Null is a religous experience, so don't even try going there!
> Null is
> > 'undefined' and guess what, Null does not equal itself (this
> seems silly
> > to me, oops, here come the flames, since undefined is certainly
> the same
> > as undefined, but religion sometimes doesn't make sense from the
> outside.
>
> Beg to differ. And I will go there. SQL trekking through Oracle
> with shields
> on full, phasers charged and photon torpedos loaded.
>
> I do not think it is at all confusing. Unless you happen to live
> in Choas
> Space. But then that would mean SQL is not a language for database
> communication, but is instead a frothy pink substance that is
> created by
> sound of opening the door of a motorcycle.
>
> You have a VARCHAR2 column called x.
>
> You do the following:
> INSERT INTO foo ( x ) VALUES ( '' )
>
> Q. What is stored in column x?
> A. ASCII/UniCode characters. Internally that is represented by a
> number,
> aka the ordinal value of the character.
>
> Q. What is the ordinal value of the character in between the quotes
> that you are attempting to insert?
> A. Undefined. There is no character value in between the quotes. Thus
> there can not be an ordinal value for it.
>
> Q. How does Oracle store a value that is not given, i.e. the "nothing
> character" that is specified by that VALUES ( '' ) clause?
> A. Exactly what you told it to store - nothing. Nada. No value. Zip.
> Zilch. Niks. I.E. a friggen NULL.
>
> Q. Is NULL a value?
> A. No it is not. The above Q&A has just proven that NULL is NOT a
> value.
> NULL is not CHAR(255) or CHAR(13) or CHAR(0). CHAR(0) is still a
> character _with_ a value - an ordinal value of zero!
> You did not say:
> INSERT INTO foo ( x ) VALUES ( CHAR(0) )
> You said:
> INSERT INTO foo ( x ) VALUES ( '' )
> Which is why:
> x = NULL will always be FALSE as that is a value comparison and
> NULL
> is not a value
> Solution - use boolean algebra to see if the column has a value, or
> whether the column has not a value, i.e.
> x IS NULL?
>
> Friggen straight forward and simple. Don't know why some wants
> to make a
> warp core breach out of it.
>
> The Ferengi mentality of some.. trying to pawn of nulls as values
>
> --
> Billy

What you say above is true ... of the way Oracle treats the empty string '', i.e. as synonymous with NULL. However, just because Oracle does something a certain way, doesn't make it correct.

Logically, '' is not the same as NULL, any more than 0 is the same as NULL for a NUMBER column. '' is a value, NULL is not.

Oracle has had to violate some of the rules for NULLs to accommodate this confusion, e.g. 'ABC'||NULL returns 'ABC' when it should return NULL (but 'ABC'||'' should return 'ABC', as it does).

I suggest it is you that have been confused by Oracle's conflation of '' and NULL!

--
Posted via http://dbforums.com
Received on Wed Jun 11 2003 - 11:02:11 CDT

Original text of this message

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