Oracle SQL-standard

What is Oracle's SQL standard?  Can someone point me to a web link?  Also, what is the current ANSI standard for SQL?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I'm not sure I understand the question.

Are you looking for what ANSI SQL commands are in the generic ANSI standard and what level of that standard Oracle Supports?

If you are looking to write ANSI generic SQL that can be executed across many database platforms, I believe you will be disappointed.  Every database vendor has their own extensions of SQL that provide capability outside of the ANSI standard.  These extension also tend to perform well inside the product they were developed for,

>>Also, what is the current ANSI standard for SQL?
By 1986, ANSI and ISO standard groups officially adopted the standard "Database Language SQL" language definition. New versions of the standard were published in 1989, 1992, 1996, 1999, 2003, 2006, 2008, 2011, and most recently, 2016. [15]
Russ SuterCommented:
All good SQL server databases should fully support the current ANSI standard. I emphasize should because there are usually some minor variations but you can generally expect them to be 95% or more ANSI compatible.

Oracle uses a SQL dialect known as PL/SQL or Procedural Language/SQL. Microsoft, by contrast, uses T-SQL or Transact-SQL. Both are quite capable but have several unique characteristics. Knowing the particular dialect will help you go beyond what ANSI SQL is capable of and allow you to write queries in a simpler, easier to read format.
PortletPaulEE Topic AdvisorCommented:
Most DBMS vendors publish their documentation on-line these days.
Please note that the PL/SQL supported by Oracle may differ by version, so keep this in mind. e.g.

Oracle 10g (Release 2):
Oracle 12c (Release 1):

for example an APPLY operator and LATERAL inline views (both part of ANSI SQL standards) are present in 12c but not before that version)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
>>Oracle uses a SQL dialect known as PL/SQL or Procedural Language/SQL

Slight correction:  SQL in Oracle is called SQL.  PL/SQL is Oracle's programming language and is pretty much Ada.  Can PL/SQL contain SQL, yes.  Can SQL contain PL/SQL, no.
Geert GOracle dbaCommented:
Slight correction ... 2
Can SQL contain pl/sql, as of 12c ... yes, in the with clause
slightwv (䄆 Netminder) Commented:
@Geert:  Thanks for the correction!  I keep forgetting about that new feature!!!
Geert GOracle dbaCommented:
don't forget about the joins (+) on multiple tables too.
That feature bit me last week ...

found out when they had to go-live with some querries from test (12.1) to production (11.2)
sometimes business keeps postponing the upgrade dates ... and then they nag when it goes wrong
Russ SuterCommented:
Slight correction:  SQL in Oracle is called SQL.  PL/SQL is Oracle's programming language and is pretty much Ada.  Can PL/SQL contain SQL, yes.  Can SQL contain PL/SQL, no.

I'm not sure I see much of a functional distinction. If you connect to an Oracle database then PL/SQL is available to you. Also, Oracle's interpretation of SQL doesn't always conform to ANSI standards. Take this example:
    Contacts c
    c.AreaCode = '800',
    c.Prefix = '555'
    c.Suffix = '5000'
    c.Company = 'ABC International'

Open in new window

That won't work on most ANSI compatible systems since table aliasing is only allowed on SELECT statements. INSERT, UPDATE, and DELETE should not use table aliases.

Then there are little details like the string concatenation operator. In Microsoft SQL you concatenate strings using '+'. In Oracle you use '||'. I don't know which one, if either one, is ANSI compliant or if they're just unique implementations in each platform.
PortletPaulEE Topic AdvisorCommented:
The double pipe concatenation is ANSI standard compatible, it is the use of + in T-SQL for concatenation that is out-of-step
Russ SuterCommented:
I have always wondered about that. Can you point me to the ANSI standard documentation that verifies this? I might have just won an office bet. :)
slightwv (䄆 Netminder) Commented:
If my Google-fu is correct you are looking for the standard: "E021-07"

How about a Microsoft site stating they don't conform?
Transact-SQL does not support this feature. See the + (Add) operator [MSDN-Add] for equivalent functionality to the concatenation operator (||) operator in [ISO/IEC9075-2:2011].

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.