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

On Fri, 11 Mar 2005 08:49:18 -0800, DA Morgan <damorgan_at_x.washington.edu> 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

 The example given on page 38 does not deal with Oracle's treatment of '' as NULL - rather it demonstrates the converse, that it treats NULL as NULL in the Standard sense when used in comparisons. The example given shows a port from SQL Server where the developers were surprised that a condition involving NULL (and Tom explicitly writes that the variable was set to NULL, not empty string) was never true.

 Apparently in some configurations of SQL Server, a comparison of a non-empty string with NULL can be treated as if the NULL were an empty string and so yields false, i.e. the slightly forced expression NOT('not null' != NULL) is TRUE.
>"SQL is supposed to operate under tri-alued logic and Oracle implements
>Null values the way ANSI SQL requires them to be implemented."

 Indeed, in the example given, the behaviour expected by the T-SQL developers was wrong compared with the Standard and general conventions of NULL in SQL, and Oracle's was correct - Boolean expressions involving comparison with NULL end up UNKNOWN. 'not null' != NULL is neither true nor false; it is unknown. NOT('not null' != NULL) is similarly unknown.

>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.

 Tom didn't get it wrong. However, there are additional issues with Oracle's treatment of empty string versus NULL that are the subject of the rest of this thread, that aren't covered by the example given in that page of One-on-one.

 Oracle's non-Standard treatment of empty strings as NULL, except when used in concatenation where NULL is treated as either an empty string or NULL depending on whether it would produce an empty string again, is never going to change, as again it would break thousands of applications and it's Just How Oracle Works. But denying it's a difference compared with the Standard doesn't help; Oracle documents this as a difference compared with Standard (more precisely - they claim full compliance with SQL-92 Entry Level, but only partial compliance with SQL-92 Transitional due to this issue), and there's references in the thread I referenced in another reply as well.

 This doesn't make Oracle "bad", and that's one of the major points of that chapter of One-on-one; different databases work differently, and you should always try to fully understand the way the one you're using (e.g. Oracle) works so you can write effective code that takes advantage of the database, not works against it.

 If you want another Tom Kyte quote, try here: http://asktom.oracle.com/pls/ask/f?p=4950:8:7967215895365162726::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:5984520277372

"'' when assigned to a varchar2(1) becomes '' which is a zero length string and a zero length string is NULL in Oracle (it is no long '')"

 And, with another bit of a typo:

"the docs are telling you want ansi says vs what we do. "

 (Presumably 'want' should be 'what').

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool