Re: [Q] Porting SQL to Oracle

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... Received on Fri Jun 13 2003 - 17:14:36 CDT