Re: [Q] Porting SQL to Oracle

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Sat, 14 Jun 2003 10:10:50 -0700
Message-ID: <3EEB5719.596F0280@exxesolutions.com>

Tony wrote:

> Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EE9F65C.D9380475_at_exxesolutions.com>...
> > Tony wrote:
> >
> > > Billy Verreynne <vslabs_at_onwe.co.za> wrote in message news:<bcbtjt$d57$1_at_ctb-nnrp2.saix.net>...
> > > > Treating NULL as a state (which it is from an OO perpective), Oracle's way
> > > > of dealing with NULL is not strange at all. It works exactly the same way
> > > > in the OO code I write.
> > >
> > > I don't have a copy of the SQL:1999 standard to refer to, but Oracle's
> > > own documentation states that Oracle doesn't conform to the standard
> > > on '' v. NULL:
> > >
> > > [Quote From Oracle SQL Reference]
> > > 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)
> > > [/Quote From Oracle SQL Reference]
> > >
> > > I'm with SQL:1999 on this. (Quite what your OO code has to do with
> > > the meaning of NULL in SQL still mystifies me).
> >
> > Actually Oracle does conform to the standard. Some other products do not, by default, but have a
> > parameter setting to make themselves like Oracle so that they can claim ANSI compliance.
> >
> > See Tom Kyte's discussion of this in Chapter 1 of "Expert one-on-one Oracle."

> Well, Oracle doesn't fully conform, by its own admission (see my quote
> above). But having re-read Kyte as you suggest, I see that SQL Server
> doesn't either - but differently.

> The example goes something like this:

> declare
> v_value varchar2(30);
> begin
> for r in (select * from t where col=v_value)
> ...

> SQL Server will apparently return any rows from t where col is NULL
> (or possibly only where it is '', which is different in SQL Server and
> ANSI - I don't know).

> If so, SQL Server is wrong to do so, because v_value is clearly NULL,
> and nothing is equal to NULL. However, I believe the SQL Server
> version would be correct if the following small change was made:

> declare
> v_value varchar2(30) := '';
> begin
> for r in (select * from t where col=v_value)
> ...

> This should correctly return rows from t where col='', but NOT where
> col is NULL!

>
> I fear I am starting to look somewhat cranky...

Then sit back, relax, have a cognac and listen to some Bowie or Pink Floyd.

ANSI complaince is a joke. Any thoughts that two RDBMS products do, or should, work the same, unrelated to reality. Learn how each does it and cash your paycheck.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sat Jun 14 2003 - 12:10:50 CDT