Oracle Compliance To Core SQL:2008

The ANSI and ISO SQL standards require conformance claims to state the type of conformance and the implemented facilities. The minimum claim of conformance is called Core SQL:2008 and is defined in Part 2, SQL/Foundation, and Part 11, SQL/Schemata, of the standard. The following products provide full or partial conformance with Core SQL:2008 as described in the tables that follow:

Oracle Database server

Pro*C/C++, release 9.2.0

Pro*COBOL, release 9.2.0

Pro*Fortran, release 1.8.77

SQL Module for Ada (Mod*Ada), release 9.2.0

Pro*COBOL 1.8, release 1.8.77

Pro*PL/I, release 1.6.28

OTT (Oracle Type Translator)

The SQL standards conformance features can be used either as a guide to portability, or as a guide to functionality. From the standpoint of portability, the user is interested in conformance to both the precise syntax and semantics of the standard feature. From the standpoint of functionality, the user is less concerned about the precise syntax and more concerned with issues of semantics. The tables in this appendix use the following terms regarding support for standard syntax and semantics:

Full Support: The feature is supported with standard syntax and semantics.

Partial Support: Some, but not all, of the standard syntax is supported; whatever is supported has standard semantics.

Enhanced Supported: The standard semantics is supported, but gives functionality that differs from the standard by enhancing it.

Equivalent Support: The standard semantics is supported using non-standard syntax.

Similar Support: Neither the standard's syntax nor semantics are supported precisely, but similar functionality is provided.

Oracle's support for the features of Core SQL:2008 is listed in Table C-1:

E021-03, Character literals (Oracle regards the zero-length literal '' as being null)

E021-12, Character comparison (Oracle's rules for padding the shorter of two strings to be compared differs from the standard)

Oracle has equivalent functionality for these subfeatures:

E021-04, CHARACTER_LENGTH function: use LENGTH function instead

E021-05, OCTET_LENGTH function: use LENGTHB function instead

E021-06, SUBSTRING function: use SUBSTR function instead

E021-11, POSITION function: use INSTR function instead

E031, Identifiers

Oracle supports this feature, with the following exceptions:

Oracle does not support the escape sequence to permit a double quote within a quoted identifier

A non-quoted identifier may not be equivalent to an Oracle reserved word (the list of Oracle reserved words differs from the standard's list)

A column name may not be ROWID, even as a quoted identifier

Oracle extends this feature as follows:

An identifier may be up to 30 characters long

A non-quoted identifier may have dollar sign ($) or pound sign (#)

E051, Basic query specification

Oracle fully supports the following subfeatures:

E051-01, SELECTDISTINCT

E051-02, GROUPBY clause

E051-04, GROUPBY can contain columns not in SELECT list

E051-05, SELECT list items can be renamed

E051-06, HAVING clause

E051-07, Qualified * in SELECT list

Oracle partially supports the following subfeatures:

E051-08, Correlation names in FROM clause (Oracle supports correlation names, but not the optional AS keyword)

Oracle has equivalent functionality for the following subfeature:

E051-09, Rename columns in the FROM clause (column names can be renamed in a subquery in the FROM clause)

E061, Basic predicates and search conditions

Oracle fully supports this feature, except that Oracle comparison of character strings differs from the standard as follows: In the standard, two character strings of unequal length are compared by either padding the shorter string with spaces or a fictitious character that is less than all actual characters. The decision on padding is made on the basis of the character set. In Oracle, the decision is based on whether the comparands are of fixed or varying length.

E071, Basic query expressions

Oracle fully supports the following subfeatures:

E071-01, UNIONDISTINCT table operator

E071-02, UNIONALL table operator

E071-05, Columns combined by table operators need not have exactly the same type

Oracle fully supports all subfeatures of this feature, except E081-09, USAGE privileges. In the standard, the USAGE privilege permits the user to use domains, collations, character sets, transliterations, user-defined types and sequence generators. Oracle does not support domains or transliterations. No privileges are required to access collations and character sets. The Oracle privilege to use a user-defined type is EXECUTE. The Oracle privilege to use a sequence type is SELECT.

E121-17, WITHHOLD cursors (in the standard, a cursor is not held through a ROLLBACK, but Oracle does hold through ROLLBACK)

E131, Null value support

Oracle fully supports this feature, with this exception: In Oracle, a null of character type is indistinguishable from a zero-length character string.

E141, Basic integrity constraints

Oracle fully supports this feature.

E151, Transaction support

Oracle fully supports this feature.

E152, Basic SETTRANSACTION statement

Oracle fully supports this feature.

E153, Updatable queries with subqueries

Oracle fully supports this feature.

E161, SQL comments using leading double minus

Oracle fully supports this feature.

E171, SQLSTATE support

Oracle fully supports this feature.

E182, Module language

Oracle supports this feature for Ada only.

F021, Basic information schema

Oracle does not have any of the views in this feature. However, Oracle makes the same information available in other metadata views:

Instead of TABLES, use ALL_TABLES.

Instead of COLUMNS, use ALL_TAB_COLUMNS.

Instead of VIEWS, use ALL_VIEWS.

However, Oracle's ALL_VIEWS does not display whether a user view was defined WITHCHECKOPTION or if it is updatable. To see whether a view has WITHCHECKOPTION, use ALL_CONSTRAINTS, with TABLE_NAME equal to the view name and look for CONSTRAINT_TYPE equal to 'V'.

Instead of TABLE_CONSTRAINTS, REFERENTIAL_CONSTRAINTS, and CHECK_CONSTRAINTS, use ALL_CONSTRAINTS.

However, Oracle's ALL_CONSTRAINTS does not display whether a constraint is deferrable or initially deferred.

F031, Basic schema manipulation

Oracle fully supports these subfeatures:

F031-01, CREATETABLE statement to create persistent base tables

F031-02, CREATEVIEW statement

F031-03, GRANT statement

Oracle partially supports this subfeature:

F031-04, ALTERTABLE statement: ADDCOLUMN clause (Oracle does not support the optional keyword COLUMN in this syntax. Also, Oracle requires the column definition to be enclosed in parentheses, unlike the standard.)

Oracle does not support these subfeatures (because Oracle does not support the keyword RESTRICT):

F031-13, DROPTABLE statement: RESTRICT clause

F031-16, DROPVIEW statement: RESTRICT clause

F031-19, REVOKE statement: RESTRICT clause

(Oracle DROP commands enhance the standard by invalidating dependent objects, so that they can be subsequently revalidated without user action, rather than either cascading all drops to dependent objects or prohibiting a drop if there is a dependent object.)

F041, Basic joined table

Oracle fully supports this feature.

F051, Basic date and time

Oracle fully supports this feature, except the following subfeatures are not supported:

F051-02, TIME data type

F051-07, LOCALTIME

F081, UNION and EXCEPT in views

Oracle fully supports UNION in views. The equivalent in Oracle of the standard's EXCEPT is called MINUS, which is fully supported in views.

F131, Grouped operations

Oracle fully supports this feature.

F181, Multiple module support

Oracle fully supports this feature.

F201, CAST function

Oracle fully supports this feature.

F221, Explicit defaults

Oracle fully supports this feature.

F261, CASE expressions

Oracle fully supports this feature.

F311, Schema definition statement

Oracle fully supports this feature.

F471, Scalar subquery values

Oracle fully supports this feature.

F481, Expanded null predicate

Oracle fully supports this feature.

F501, Feature and conformance views

Oracle does not support this feature.

F812, Basic flagging

Oracle has a flagger, but it flags SQL-92 compliance rather than SQL:2008 compliance.

S011, Distinct types

Distinct types are strongly typed scalar types. A distinct type can be emulated in Oracle using an object type with only one attribute. The standard's Information Schema view called USER_DEFINED_TYPES is equivalent to Oracle's metadata view ALL_TYPES.

T321, Basic SQL-invoked routines

Oracle fully supports these subfeatures:

T321-03, function invocation

T321-04, CALL statement

Oracle supports these subfeatures with syntactic differences:

T321-01, user-defined functions with no overloading

T321-02, user-defined procedures with no overloading

The Oracle syntax for CREATEFUNCTION and CREATEPROCEDURE differs from the standard as follows:

In the standard, the mode of a parameter (IN, OUT, or INOUT) comes before the parameter name, whereas in Oracle it comes after the parameter name.

The standard uses INOUT, whereas Oracle uses INOUT.

Oracle requires either IS or AS after the return type and before the definition of the routine body, while the standard lacks these keywords.

If the routine body is in C (for example), then the standard uses the keywords LANGUAGECEXTERNALNAME to name the routine, whereas Oracle uses LANGUAGECNAME.

If the routine body is in SQL, then Oracle uses its proprietary procedural extension called PL/SQL.

Oracle supports the following subfeature in PL/SQL but not in Oracle SQL:

T321-05, RETURN statement

Oracle provides equivalent functionality for the following subfeatures:

T321-06, ROUTINES view: Use the ALLPROCEDURES metadata view.

T321-07, PARAMETERS view: Use the ALL_ARGUMENTS and ALL_METHOD_PARAMS metadata views.

T631, IN predicate with one list element

Oracle fully supports this feature.

Scripting on this page enhances content navigation, but does not change the content in any way.