Oracle Compliance To Core SQL:2008

E011, Numeric data types

Oracle fully supports this feature.

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

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)

• 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, 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 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, UNION DISTINCT table operator

• E071-02, UNION ALL table 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

E081, Basic privileges

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.

E091, Set functions

Oracle fully supports this feature.

E101, Basic data manipulation

Oracle fully supports this feature.

E111, Single row SELECT statement

Oracle fully supports this feature.

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 provides enhanced support for 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)

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

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

(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 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 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 ALL PROCEDURES 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.