Oracle SQL-standard

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]

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

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

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

@Geert:  Thanks for the correction!  I keep forgetting about that new feature!!!

@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

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'

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

The double pipe concatenation is ANSI standard compatible, it is the use of + in T-SQL for concatenation that is out-of-step

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