Re: Is Oracle SQL99 Compliant?

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Fri, 14 Jan 2005 20:02:23 -0800
Message-ID: <34ritfF4eq0b8U1@individual.net>

Thomas Kyte wrote:
> In article <34q2tiF49iiv3U1_at_individual.net>, Serge Rielau says...
>

>>nitin2276_at_gmail.com wrote:
>>
>>>Ya, Oracle is following  SQL99 standards. So it is SQL99 compliant.
>>>>From the release of Oracle 9i onwards, Oracle has also started
>>>following ANSI standards.
>>>
>>>Regards
>>>
>>>Nitin
>>>
>>>seapearl1023_at_ms65.url.com.tw wrote:
>>>
>>>
>>>>Hi,
>>>>
>>>>My question is if Oracle Standard version is SQL99 Compliant? How
>>>
>>>about
>>>
>>>
>>>>Oracle Enterprise version?
>>>>
>>>>Thanks.
>>>>
>>>>Hai-Chu
>>>
>>>
>>I beg to differ. What about NULL = '', Trigger/RI semantics?

>
>
> what about null = <anything>. it is unknown:
>
> ops$tkyte_at_ORA9IR2> select * from dual where null = '';
> no rows selected
>
> ops$tkyte_at_ORA9IR2> select * from dual where null <> '';
> no rows selected

Interesting. I didn't know that
>
> where is the problem there? there is the fact that '' is null upon insert, but
> that is not NULL=''.

'' || NULL = '' Am I mistaken?

> actually, Oracle was the first database every to complete NIST sql92
> certification (i know, i was on the team that did it, i had the shirt and
> all)....
>
> Oracle doesn't and didn't have a FRED that describe the differences between the
> umpteen releases on every platform (that would be db2 and all of its "family of
> db productions, some 7 or 8 relational database and who knows how many
> non-relational...)

LOL Shame on DB2 that IMS and U2 are not SQL compliant ;-) You will need to dig fairly deep to find DML statements which are incompatible amongst the DB2 products.
In the times of FRED DB2 CS was just a baby running on OS/2 and AIX. Here is the cross-platform SQL Reference manual: http://www-128.ibm.com/developerworks/db2/library/techarticle/0206sqlref/0206sqlref.html

But you are right: Oracle is 100% compatible with Oracle and integrates 100% with Oracle. Stay in Ora-land and you shall be safe. In the meantime the world moves on and finds itself the next attempt to standardize. XQuery.. another child which seems to die at birth.

> and you do know that there is nary a vendor that can even begin to partially
> sort of claim "sql-99 we got it all compliance" right?
I posted that in the SQL Server ng where the poster also posted. There is no test.

>>While Oracle does implement many SQL-99 features it has
>>a) numerous extensions that are unlikely to ever make it into the 
>>standard because they don't adhere to the foundation of SQL

> but make it run really fast. If we were truly all the "same" what would be the
> point?

Not the same. But implement features so they are standardizable and not doomed to be proprietary.

> and why does "going way above and beyond" mean "bad thing"?
See above, pushing is good, making a mess is not.

>>b) mumerous idiosynchrecies which break portability (like NULL = ''). >>

> lets talk concurrency controls sometime -- which basically prohit portability
> for any real app with more than about 10 lines of code that include "insert,
> update, delete or merge".

I have ironically never found that to be a problem in my migrations. Again I don't have a problem with added features. Who knows maybe someday Oracle wants to put the feature into the standard or do we need to wait for IBM just like with sequences or recursion?

> oh but wait, lets try to port our DB2 mainframe app to db2 "udb" -- without
> change, all of the time. portability within the same "product" should be "easy"
> no?

A lot easier than between Oracle and DB2, yes.

>>.. at least SQL Server adds ANSI compatibility knobs. Oracle doesn't >>even care that much.

> fips flaggers don't count? not sure where you were going here?
FIPS is a tad old now isn't it.
Does that switch turn of the old outer join syntax?

> how many NIST certs did db2 get before NIST stopped doing it?
I know it was certified.. for anything else .. I claim late birth :-)

Cheers
Serge Received on Fri Jan 14 2005 - 22:02:23 CST