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?
LVL 1
sdrussAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
If my Google-fu is correct you are looking for the standard: "E021-07"

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Oracle-Compliance-To-Core-SQL2011.html#GUID-D372D906-805B-49B8-824A-D4697B05B7F8


How about a Microsoft site stating they don't conform?
https://msdn.microsoft.com/en-us/library/hh501243(v=sql.105).aspx
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].
0
 
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?
https://en.wikipedia.org/wiki/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]
0
 
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.

https://www.seguetech.com/microsoft-sql-server-vs-oracle-same-different/

https://en.wikipedia.org/wiki/PL/SQL
https://en.wikipedia.org/wiki/Transact-SQL
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
PortletPaulConnect With a Mentor freelancerCommented:
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): https://docs.oracle.com/cd/B19306_01/index.htm
Oracle 12c (Release 1): https://docs.oracle.com/database/121/index.html

for example an APPLY operator and LATERAL inline views (both part of ANSI SQL standards) are present in 12c but not before that version)
0
 
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.
0
 
Geert GOracle dbaCommented:
Slight correction ... 2
Can SQL contain pl/sql, as of 12c ... yes, in the with clause
https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1
0
 
slightwv (䄆 Netminder) Commented:
@Geert:  Thanks for the correction!  I keep forgetting about that new feature!!!
0
 
Geert GOracle dbaCommented:
@Steve,
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
0
 
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:
UPDATE
    Contacts c
SET
    c.AreaCode = '800',
    c.Prefix = '555'
    c.Suffix = '5000'
WHERE
    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.
0
 
PortletPaulfreelancerCommented:
The double pipe concatenation is ANSI standard compatible, it is the use of + in T-SQL for concatenation that is out-of-step
0
 
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. :)
0
All Courses

From novice to tech pro — start learning today.