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

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Fri, 11 Mar 2005 18:09:30 +0100
Message-ID: <d0sj7e$8ro$1@news6.zwoll1.ov.home.nl>

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.

-- 
Regards,
Frank van Bortel

Received on Fri Mar 11 2005 - 11:09:30 CST