Re: migrating from sql server 2000 to oracle 9i-r2

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 11 Mar 2005 14:57:09 -0800
Message-ID: <1110581636.851460@yasure>

Frank van Bortel wrote:

> DA Morgan wrote:
>

>> Frank van Bortel wrote:
>>
>>> Oracle treats '' as NULL - this is not ANSI SQL compliant,
>>> as correctly stated before.
>>
>>
>>
>> I do not believe you are correct. It is Oracle that is ANSI
>> compliant and SQL Server that is not.
>>
>> Reference:
>> Tom Kyte
>> Expert one-on-one Oracle
>> Chapter 1, page 38
>>
>> "SQL is supposed to operate under tri-alued logic and Oracle implements
>> Null values the way ANSI SQL requires them to be implemented."
>>
>> and
>>
>> "For example SQL Server supports the ANSI method of Null comparison,
>> just not by default (it would break thousands of existing legacy
>> applications built on that database)."
>>
>> Just a wild hunch ... but I doubt Tom got it wrong.

>
>
> Yes, well, that was not the issue.
> The issue was about '', not about NULL being NULL not being NULL:
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
> PL/SQL Release 9.2.0.6.0 - Production
> CORE 9.2.0.6.0 Production
> TNS for Linux: Version 9.2.0.6.0 - Production
> NLSRTL Version 9.2.0.6.0 - Production
>
> hr_at_O920.CSDB01.CS.NL> select * from dual where null = '';
> no rows selected
>
> hr_at_O920.CSDB01.CS.NL> select * from dual where null != '';
> no rows selected
>
> Ergo: '' is treated as NULL. And THAT is NOT ansi compliant.

What you did in 9.2.0.6 proves nothing as that is the way Oracle has always worked. It is your words alone that claim this behaviour non-compliant.

So lets try this again ... Tom Kyte specifically states that it is compliant ... and I referenced the specific page of the specific book in which he did.

So if you think Tom is wrong then I'd suggest rather than showing us SQL statements that prove nothing ... you specifically address where Mr. Kyte is incorrect.

Me? I'm going to sit back with a scotch and watch.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri Mar 11 2005 - 16:57:09 CST