Oracle Compliance To Core SQL:2003

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:2003 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:2003 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, release 9.2.0.
OTT8, release 8.1.8

The Core SQL:2003 features that Oracle fully supports are listed in Table B-1:

Table B-1 Fully Supported Core SQL:2003 Features

Feature ID	Feature
E011	Numeric data types
E031	Identifiers
E061	Basic predicates and search conditions
E081	Basic privileges
E091	Set functions
E101	Basic data manipulation
E111	Single row `SELECT` statement
E131	Null value support (nulls in lieu of values)
E141	Basic integrity constraints
E151	Transaction support
E152	Basic `SET` `TRANSACTION` statement
E153	Updatable queries with subqueries
E161	SQL comments using leading double minus
E171	`SQLSTATE` support
F041	Basic joined table
F051	Basic date and time
F081	`UNION` and `EXCEPT` in views
F131	Grouped operations
F181	Multiple module support
F201	`CAST` function
F221	Explicit defaults
F261	`CASE` expressions
F311	Schema definition statement
F471	Scalar subquery values
F481	Expanded `NULL` predicate

The Core SQL:2003 features that Oracle partially supports are listed in Table B-2:

Table B-2 Partially Supported Core SQL:2003 Features

Feature ID, Feature	Partial Support
E021, Character data types	Oracle fully supports these subfeatures: E021-01, `CHARACTER` data type E021-07, Character concatenation E021-08, `UPPER` and `LOWER` functions E021-09, `TRIM` function E021-10, Implicit casting among character data types E021-12, Character comparison Oracle partially supports these subfeatures: E021-02, `CHARACTER` `VARYING` data type (Oracle does not distinguish a zero-length `VARCHAR` string from `NULL`) E021-03, Character literals (Oracle regards the zero-length literal `''` as being null) 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
E051, Basic query specification	Oracle fully supports the following subfeatures: E051-01, `SELECT` `DISTINCT` E051-02, `GROUP` `BY` clause E051-04, `GROUP` `BY` 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 does not support the following subfeature: E051-09, Rename columns in the `FROM` clause
E071, Basic query expressions	Oracle fully supports the following subfeatures: E071-01, `UNION` `DISTINCT` table operator E071-02, `UNION` `ALL` able operator E071-05, Columns combined by table operators need not have exactly the same type E071-06, table operators in subqueries Oracle has equivalent functionality for the following subfeature: E071-03, `EXCEPT` `DISTINCT` table operator: Use `MINUS` instead of `EXCEPT` `DISTINCT`
E121, Basic cursor support	Oracle fully supports the following subfeatures: E121-01, `DECLARE` `CURSOR` E121-02, `ORDER` `BY` columns need not be in select list E121-03, Value expressions in `ORDER` `BY` clause E121-04, `OPEN` statement E121-06, Positioned `UPDATE` statement E121-07, Positioned `DELETE` statement E121-08, `CLOSE` statement E121-10, `FETCH` statement, implicit `NEXT` Oracle partially supports the following subfeatures: E121-17, `WITH` `HOLD` cursors (in the standard, a cursor is not held through a `ROLLBACK`, but Oracle does hold through `ROLLBACK`)
F031, Basic schema manipulation	Oracle fully supports these subfeatures: F031-01, `CREATE` `TABLE` statement to create persistent base tables F031-02, `CREATE` `VIEW` statement F031-03, `GRANT` statement Oracle partially supports this subfeature: F031-04, `ALTER` `TABLE` statement: `ADD` `COLUMN` clause (Oracle does not support the optional keyword `COLUMN` in this syntax) Oracle does not support these subfeatures (because Oracle does not support the keyword `RESTRICT`): F031-13, `DROP` `TABLE` statement: `RESTRICT` clause F031-16, `DROP` `VIEW` statement: `RESTRICT` clause F031-19, `REVOKE` statement: `RESTRICT` clause
F812, Basic flagging	Oracle has a flagger, but it flags SQL-92 compliance rather than SQL:2003 compliance
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 `CREATE` `FUNCTION` and `CREATE` `PROCEDURE` 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 `IN` `OUT`. 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 `LANGUAGE` `C` `EXTERNAL` `NAME` to name the routine, whereas Oracle uses `LANGUAGE` `C` `NAME`. If the routine body is in SQL, then Oracle uses its proprietary procedural extension called PL/SQL. Oracle supports the following subfeatures in PL/SQL but not in Oracle SQL: T321-05, `RETURN` statement

Oracle has equivalent functionality for the features listed in Table B-3:

Table B-3 Equivalent Functionality for Core SQL:2003 Features

Feature ID, Feature	Equivalent Functionality
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 `WITH` `CHECK` `OPTION` or if it is updatable. To see whether a view has `WITH` `CHECK` `OPTION`, 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.
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.
T695, Translation support	The Oracle `CONVERT` function can convert between many character sets. Oracle does not provide the ability to add or drop character set conversions.

The Core SQL:2003 features that Oracle does not support are listed in Table B-4:

Table B-4 Unsupported Core SQL:2003 Features

Feature ID	Feature
F501	Features and conformance views

Note:

Oracle does not support E182, Module language. Although this feature is listed in Table 35 in SQL/Foundation, it merely indicates that Core consists of a choice between Module language and embedded language. Module language and embedded language are completely equivalent in capability, differing only in the manner in which SQL statements are associated with the host programming language. Oracle supports embedded language.