Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Standard

Re: SQL Standard

From: Hans Forbrich <forbrich_at_telusplanet.net>
Date: Fri, 16 Nov 2001 08:30:32 GMT
Message-ID: <3BF4D089.AA5B2B85@telusplanet.net>


About your chain-yanking comment, I nearly fell off my chair laughing. Oracle has never, ever, claimed that their PL/SQL - the programming language frequently used in stored procedures - is SQL; different beasts entirely. Last time I looked, you could not program any procedures at all in SQL, which is why every vendor took a different approach to stuff like triggers, etc.

Oracle's deviations from standard are clearly denoted in their SQL Langauge Refernce Manual (not PL/SQL Language Reference Manual) and these deviations have been becoming smaller over time.

Nicholas Carey wrote:

> On 07 Nov 2001, Chariya Peterson <Chariya.Peterson_at_noaa.gov>
> spake and said:
>
> > I am looking for information on SQL standard. Would
> > appreciate it if anyone could post url's and or faqs for me?
> > I am gathering information on how much of the standard (what
> > ever exists, which ever version) is supported by each RDBMS
> > (at least by Oracle, Informix, DB2, Sybase, My-SQL), and
> > information on each RDBMS extension beyond the standard.
> > I'd like to know how realistic it is to develop software
> > whose database queries are RDBMS independent.
>
> You can buy the Standard from an ANSI/ISO affiliate near you. But
> then you'll have to turn into a language lawyer to make sense of
> it. However, this is a good book:
>
> A Guide to the Sql Standard:
> A User's Guide to the Standard
> Database Language Sql
> Chris J. Date, Hugh Darwen (Contributor)
> Addison-Wesley, 1997.
> ISBN: 0201964260
>
> AFAIK, most DB vendors claim compliance with SQL-92 at the most
> minimal level: 'entry-level'. It has been quite a while since I
> looked at that, though. And most DB venders do extend SQL quite a
> bit, especially with regard to stored procedures, triggers, etc.
>
> Oracle may be (this should yank some chains!) the biggest
> offender in this regard, since they've turned SQL into a full-
> blown, ADA-derived programming language.
>
> Micro$oft, on the other hand has committed to bringing SQL Server
> to full ANSI-compliance. Which isn't to say that they'll get rid
> of their extensions. :)
>
> So you can write code that complies with 'entry-level' SQL-92 and
> expect to have it -- more-or-less -- run. It will almost
> certainly be easier to port to the target RDBMS. But I think that
> what will bite you more is the non-standard way in which
> different RDBMSs do stuff internally. Collating Sequence varies.
> Some RDBMSs are case-sensitive WRT character string comparisons;
> others aren't. M$ SQL Server 2000 allows for multiple collating
> sequences and comparison rules -- just to make things difficult.
>
> A more profitable method, IMHO, for building portable databases
> is to enforce a couple of simple rules:
>
> * Thou Shalt Not Access the Database
> Except Via Stored Procedures.
>
> * Thou Shalt Isolate Thy Database Access Layer In A
> Wrapper And Place It In A Library, Where All Who
> Will May Fearlessly Use It Without Awareness Of
> What Goes On Inside the Wrapper.
>
> This isolates the problematic stuff. You can exploit the target
> DBMSs features. When it's time to move to a different database,
> the changes are all isolated from the people using the database.
>
> Done right, you can do some pretty major remodelling of the
> database and have the applications that talk to it remain pretty
> much in the dark about just what you've done -- as long as you
> leave the API alone.
>
> This does imply that the API exposed to the client represent
> logical actions within the domain of the application rather than
> simple database CRUDs.
>
> Moreover, wrapping the actual queries as stored procedures makes
> the inevitable upgrades easier. In M$ SQL Server, for instance,
> the 'upgrade wizard' that converts from SQL Server v7.0 to 2000
> scans your whole schema (including stored procedure and trigger
> source) and identifies problem areas that require attention.
>
> And it simplifies security -- each application only needs GRANT
> EXECUTE for each stored procedure they need to access.
> --
Received on Fri Nov 16 2001 - 02:30:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US