Oracle Support for Optional Features of SQL/Foundation:2003

B011

Embedded Ada

B012

Embedded C

B013

Embedded COBOL

B014

Embedded Fortran

B021

Direct SQL

(in Oracle, this is SQL-Plus)

F281

LIKE enhancements

F411

Time zone specification

F421

National character

F442

Mixed column references in set functions

F491

Constraint management

F555

Enhanced seconds precision

(Oracle supports up to 9 places after the decimal point)

F561

Full value expressions

F721

Deferrable constraints

F731

INSERT column privileges

F781

Self-referencing operations

F801

Full set function

S151

Type predicate

S161

Subtype treatment

T201

Comparable data types for referential constraints

T351

Bracketed comments

T431

Extended grouping capabilities

T441

ABS and MOD functions

T611

Elementary OLAP operators

T621

Enhanced numeric functions

B031, Basic dynamic SQL

Oracle supports this, with the following restrictions:

• Oracle supports a subset of the descriptor items.

• For <input using clause>, Oracle only supports <using input descriptor>.

• For <output using clause>, Oracle only supports <into descriptor>.

• Dynamic parameters are indicated by a colon followed by an identifier rather than a question mark.

B032, Extended dynamic SQL

Oracle only implements the ability to declare global statements and global cursors from this feature; the rest of the feature is not supported.

F034, Extended REVOKE statement

Oracle supports the following parts of this feature:

• F034-01, REVOKE statement performed by other than the owner of a schema object

• F034-03, REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTION.

Oracle provides equivalent functionality for the following parts of this feature:

• CASCADE: In Oracle, a REVOKE invalidates all dependent objects, which become effectively unusable until the metadata is changed through subsequent CREATE and GRANT commands enabling the the invalidated object to be successfully recompiled.

F052, Intervals and datetime arithmetic

Oracle only supports the INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND data types.

F111, Isolations levels other than SERIALIZABLE

In addition to SERIALIZABLE, Oracle supports the READ COMMITTED isolation level.

F191, Referential delete actions

Oracle supports ON DELETE CASCADE and ON DELETE SET NULL.

F302, INTERSECT table operator

Oracle supports INTERSECT but not INTERSECT ALL.

F312, MERGE statement

The Oracle MERGE statement is almost the same as the standard, with these exceptions:

• Oracle does not support the optional AS keyword before a table alias

• Oracle does not support the ability to rename columns of the table specified in the USING clause with a parenthesized list of column names following the table alias

• Oracle does not support the <override clause>

F391, Long identifiers

Oracle supports identifiers up to 30 characters in length.

F401, Extended joined table

Oracle supports FULL outer joins.

F403, Partitioned join tables

Oracle supports this feature, except with FULL outer joins

F441, Extended set function support

Oracle supports the following parts of this feature:

• The ability in the WHERE clause to reference a column that is defined using an aggregate, either in a view or an inline view.

• COUNT without DISTINCT of an expression.

• Aggregates that references columns that are outer references with respect to the aggregating query.

F461, Named character sets

Oracle supports many character sets with Oracle-defined names. Oracle does not support any other aspect of this feature.

F531, Temporary tables

Oracle supports GLOBAL TEMPORARY tables.

F591, Derived tables

Oracle supports <derived table>, with the exception of:

• Oracle does not support the optional AS keyword before a table alias.

• Oracle does not support <derived column list>.

F831, Full cursor update

Oracle supports the combination of FOR UPDATE and ORDER BY clauses in a query.

S111, ONLY in query expressions

Oracle supports the ONLY clause for view hierarchies; Oracle does not support hierarchies of base tables.

S162, Subtype treatment for references

The standard requires parentheses around the referenced types name; Oracle does not support parentheses in this position.

T041, Basic LOB data type support

Oracle supports the following aspects of this feature:

• The keywords BLOB, CLOB, and NCLOB.

• Concatenation, UPPER, LOWER, and TRIM on CLOBs.

Oracle provides equivalent support for the following aspects of this feature:

• Use INSTR instead of POSITION.

• Use LENGTH instead of CHAR_LENGTH.

• Use SUBSTR instead of SUBSTRING.

Oracle does not support the following aspects of this feature:

• The keywords BINARY LARGE OBJECT, CHARACTER LARGE OBJECT or NATIONAL CHARACTER LARGE OBJECT as synonyms for BLOB, CLOB and NCLOB, respectively.

• <binary string literal>

• The ability to specify an upper bound on the length of a LOB or CLOB.

• Concatenation of BLOBs

T111, Updatable joins, unions and columns

Oracle's updatable join views are a subset of the standard's updatable join capabilities.

T121, WITH (excluding RECURSIVE) in query expression

Oracle supports this, except for the ability to rename the columns following the <query name>; instead, you can rename the columns in the <select list> of the query that is the definition of the <query name>.

T122, WITH (excluding RECURSIVE) in subquery

Same restriction as Feature T121.

T211, Basic trigger capability

Oracle's triggers differ from the standard as follows:

• Oracle does not provide the optional syntax FOR EACH STATEMENT for the default case, the statement trigger.

• Oracle does not support OLD TABLE and NEW TABLE; the transition tables specified in the standard (the multiset of before and after images of affected rows) are not available.

• The trigger body is written in PL/SQL, which is functionally equivalent to the standard's procedural language PSM, but not the same.

• In the trigger body, the new and old transition variables are referenced beginning with a colon.

• Oracle's row triggers are executed as the row is processed, instead of buffering them and executing all of them after processing all rows. The standard's semantics are deterministic, but Oracle's in-flight row triggers are more performant.

• Oracle before-row and before-statement triggers can perform DML statements, which is forbidden in the standard. However, Oracle after-row statements cannot perform DML, while it is permitted in the standard.

• When multiple triggers apply, the standard says they are executed in order of definition. Iin Oracle the execution order is nondeterministic.

• Oracle uses the system privileges CREATE TRIGGER and CREATE ANY TRIGGER to regulate creation of triggers, instead of the standard's TRIGGER privilege, which is a table privilege.

T271, Savepoints

Oracle supports this feature, except:

• Oracle does not support RELEASE SAVEPOINT.

• Oracle does not support savepoint levels.

T331, Basic roles

Oracle supports this feature, except for REVOKE ADMIN OPTION FOR <role name>.

T432, Nested and concatenated GROUPING SETS

Oracle supports concatenated GROUPING SETS, but not nested GROUPING SETS.

T591, UNIQUE constraints of possibly null columns

Oracle permits a UNIQUE constraint on one or more nullable columns. If the UNIQUE constraint is on a single column, then the semantics are the same as the standard (the constraint permits any number of rows that are null in the designated column). If the UNIQUE constraint is on two or more columns, then the semantics are nonstandard. Oracle permits any number of rows that are null in all the designated columns. Unlike the standard, if a row is non-null in at least one of the designated columns, then another row having the same values in the non-null columns of the constraint is a constraint violation and not permitted.

T612, Advanced OLAP operations

Oracle supports the following elements of this feature: PERCENT_RANK, CUME_DIST, WIDTH_BUCKET, hypothetical set functions, PERCENTILE_CONT, and PERCENTILE_DISC.

Oracle does not support the following elements of this feature:

• window names

• ROW_NUMBER without an ORDER BY clause

T641, Multiple column assignment

The standard syntax to assign to multiple columns is supported if the assignment source is a subquery.

B031, Basic dynamic SQL

Oracle embedded preprocessors implement this feature, with the following modifications:

• Parameters are indicated by a colon followed by an identifier, instead of a question mark.

• Oracle's DESCRIBE SELECT LIST FOR statement replaces the standard's DESCRIBE OUTPUT.

• Oracle provides DECLARE STATEMENT if you want to declare a cursor using a dynamic SQL statement physically prior to the PREPARE statement that prepares the dynamic SQL statement.

B032, Extended dynamic SQL

Oracle's DESCRIBE BIND VARIABLES is equivalent to the standard's DESCRIBE INPUT. Oracle does not implement the rest of this feature.

B122, Routine language C

Oracle supports external routines written in C, though Oracle does not support the standard syntax for creating such routines.

F032, CASCADE drop behavior

In Oracle, a DROP command invalidates all of the dropped object's dependent objects. Invalidated objects are effectively unusable until the dropped object is redefined in such a way to allow successful recompilation of the invalidated object.

F033, ALTER TABLE statement: DROP COLUMN clause

Oracle provides a DROP COLUMN clause, but without the RESTRICT or CASCADE options found in the standard.

F121, Basic diagnostics management

Much of the functionality of this feature is provided through the SQLCA in embedded languages.

F231, Privilege tables

Oracle makes this information available in the following metadata views:

• Instead of TABLE_PRIVILEGES, use ALL_TAB_PRIVS.

• Instead of COLUMN_PRIVILEGES, use ALL_COL_PRIVS.

• Oracle does not support USAGE privileges so there is no equivalent to USAGE_PRIVILEGES.

F341, Usage tables

Oracle makes this information available in the views ALL_DEPENDENCIES, DBA_DEPENDENCIES and USER_DEPENDENCIES.

F381, Extended schema manipulation

Oracle fully supports the following elements of this feature:

• Oracle supports the standard syntax to add a table constraint using ALTER TABLE.

Oracle partially supports the following elements of this feature:

• Oracle supports the standard syntax to drop a table constraint, except that Oracle does not support RESTRICT.

Oracle provides equivalent functionality for the following elements of this feature:

• To alter the default value of a column, use the MODIFY option of ALTER TABLE.

Oracle does not support the following parts of this feature:

• DROP SCHEMA statement

• ALTER ROUTINE statement

F93, Unicode escapes in literals

The Oracle UNISTR function supports numeric escape sequences for all Unicode characters.

F402, Names column joins for LOBs, arrays and multisets

Oracle supports named column joins for columns whose declared type is nested table. Oracle does not support named column joins for LOBs or arrays.

F571, Truth value tests

Oracle's LNNVL function is similar to the standard's IS NOT TRUE.

F690, Collation support

Oracle provides functions that may be used to change the collation of character expressions.

F695, Translation support

Oracle's CONVERT function may be used to convert between character sets.

F771, Connection management

Oracle's CONNECT statement provides the same functionality as the standard's CONNECT statement, though with different syntax. Instead of using the standard's SET CONNECTION, Oracle provides the AT clause to indicate which connection an SQL statement should be performed on. Pro*COBOL lets you disconnect from a connection by using the RELEASE option of either COMMIT or ROLLBACK.

S023, Basic structured types

Oracle's object types are equivalent to structured types in the standard.

S025, Final structured types

Oracle's final object types are equivalent to final structured types in the standard.

S026, Self-referencing structured types

In Oracle, an object type OT may have a reference that references OT.

S041, Basic reference types

Oracle's reference types are equivalent to reference types in the standard.

S051, Create table of type

Oracle's object tables are equivalent to tables of structured type in the standard.

S081, Subtables

Oracle supports hierarchies of object views, but not of object base tables. To emulate a hierarchy of base tables, simply create a hierarchy of views on those base tables.

S091, Array types

Oracle VARRAY types are equivalent to array types in the standard. However, Oracle does not support storage of arrays of LOBs. To access a single element of an array using a subscript, you must use PL/SQL. Oracle supports the following aspects of this feature with nonstandard syntax:

• To construct an instance of varray type, including an empty array, use the varray type constructor.

• To unnest a varray in the FROM clause, use the TABLE operator.

S092, Arrays of user-defined types

Oracle supports VARRAYs of object types.

S094, Arrays of reference types

Oracle supports VARRAYs of references.

S095, Array constructors by query

Oracle supports this using CAST (MULTISET (SELECT ...) AS varray_type). The ability to order the elements of the array using ORDER BY is not supported.

S097, Array element assignment

In PL/SQL, you can assign to array elements, using syntax that is similar to the standard (SQL/PSM).

S201, SQL-invoked routines on arrays

PL/SQL provides the ability to pass arrays as parameters and return arrays as the result of functions.

S202, SQL-invoked routines on multisets

A PL/SQL routine may have nested tables as parameters.

A PL/SQL routine may return a nested table.

S233, Multiset locators

Oracle supports locators for nested tables.

S241, Transform functions

The Oracle Type Translator (OTT) provides the same capability as transforms.

S251, User-defined orderings

Oracle's object type ordering capabilities correspond to the standard's capabilities as follows:

• Oracle's MAP ordering corresponds to the standard's ORDER FULL BY MAP ordering.

• Oracle's ORDER ordering corresponds to the standard's ORDER FULL BY RELATIVE ordering.

• If an Oracle object type has neither MAP nor ORDER declared, then this corresponds to EQUALS ONLY BY STATE in the standard.

• Oracle does not have unordered object types; you can alter the ordering but you cannot drop it.

S271, Basic multiset support

Multisets in the standard are supported as nested table types in Oracle. The Oracle nested table data type based on a scalar type ST is equivalent, in standard terminology, to a multiset of rows having a single field of type ST and named column_value. Oracle nested table type based on an object type is equivalent to a multiset of structured type in the standard.

Oracle supports the following elements of this feature on nested tables using the same syntax as the standard has for multisets:

• The CARDINALITY function.

• The SET function.

• The MEMBER predicate.

• The IS A SET predicate.

• The COLLECT aggregate.

All other aspects of this feature are supported with non-standard syntax, as follows:

• To create an empty multiset, denoted MULTISET[] in the standard, use an empty constructor of the nested table type.

• To obtain the sole element of a multiset with one element, denoted ELEMENT (<multiset value expression>) in the standard, use a scalar subquery to select the single element from the nested table.

• To construct a multiset by enumeration, use the constructor of the nested table type.

• To construct a multiset by query, use CAST with a multiset argument, casting to the nested table type.

• To unnest a multiset, use the TABLE operator in the FROM clause.

S272, Multisets of user-defined types

Oracle's nested table type permits a multiset of structured types. Oracle does not have distinct types, so a multiset of distinct types is not supported

S274, Multisets of reference types

A nested table type can have one or more columns of reference type.

S275, Advanced multiset support

Oracle supports the following elements of this feature on nested tables using the same syntax as the standard has for multisets:

• The MULTISET UNION, MULTISET INTERSECTION and MULTISET EXCEPT operators

• The SUBMULTISET predicate.

• = and <> predicates.

Oracle does not support the FUSION or INTERSECTION aggregates.

S281, Nested collection types

Oracle permits nesting of its collection types (varray and nested table)

T042, Extended LOB support

Oracle fully supports the following elements of this feature:

• TRIM function on a CLOB argument

Oracle provides equivalent functionality for the following elements of this feature:

• BLOB and CLOB substring, supported using SUBSTR

• SIMILAR predicate, supported using REGEXPR_LIKE to perform pattern matching with a Perl-like syntax

The following elements of this feature are not supported:

• Comparison predicates with BLOB or CLOB operands

• CAST with a BLOB or CLOB operand

• OVERLAY (This may be emulated using SUBSTR and string concatenation.)

• LIKE predicate with BLOB or CLOB operands

T051, Row types

Oracle object types can be used in place of the standard's row types.

T061, UCS support

Oracle provides equivalent functionality for the following elements of this feature:

• Oracle supports the keyword CHAR instead of CHARACTERS, and BYTE instead of OCTETS, in a character datatype declaration.

• The Oracle COMPOSE function is equivalent to the standard's NORMALIZE function.

Oracle does not support the IS NORMALIZED predicate.

T071, BIGINT datatype

On many implementations, BIGINT refers to a binary integer type with 64 bits, which supports almost 19 decimal digits. The Oracle NUMBER type supports 39 decimal digits.

T131, Recursive query

Oracle's START WITH and CONNECT BY clauses can be used to perform many recursive queries

T132, Recursive query in subquery

Oracle's START WITH and CONNECT BY clauses can be used to perform many recursive queries

T141, SIMILAR predicate

Oracle provides REGEXP_LIKE for pattern patching with a Perl-like syntax.

T172, AS subquery clause in table definition

Oracle's AS subquery feature of CREATE TABLE has substantially the same functionality as the standard, though there are some syntactic differences.

T175, Generated columns

A generated column is a column of a table that is computed by an expression of other columns. Although Oracle does not support generated columns, a function-based index can be used to index on the result of an expression.

T176, Sequence generator support

Oracle's sequences have the same capabilities as the standard's, though with different syntax.

T322, Overloading of SQL-invoked functions and procedures

Oracle supports overloading of functions and procedures. However, the rules for handling certain datatype combinations are not the same as the standard. For example, the standard permits the coexistence of two functions of the same name differing only in the numeric types of the arguments, whereas Oracle does not permit this.

T323, Explicit security for external routines

The Oracle syntax AUTHID { CURRENT USER | DEFINER } when used when creating an external function, procedure, or package is equivalent to the standard's EXTERNAL SECURITY { DEFINER | INVOKER }.

T324, Explicit security for external routines

Oracle's syntax AUTHID { CURRENT USER | DEFINER } when used when creating a PL/SQL function, procedure, or package is equivalent to the standard's SQL SECURITY { DEFINER | INVOKER }.

T325, Qualified SQL parameter reference

PL/SQL supports the use of a routine name to qualify a parameter name.

T326, Table functions

Oracle provides equivalents for the following elements of this feature:

• <multiset value constructor by query> is supported using CAST (MULTISET (<query expression>) AS <nested table type>)

• <table function derived table> is supported using the TABLE operator in the FROM clause with a varray or nested table as the argument.

• <collection value expression> is equivalent to an Oracle expression resulting in a varray or nested table.

• <returns table type> is equivalent to a PL/SQL function that returns a nested table.

T433, Multiargument function GROUPING

The Oracle GROUP_ID function can be used to conveniently distinguish groups in a grouped query, serving the same purpose as the standard multiargument GROUPING function.

T471, Result sets return value

PL/SQL ref cursors provide all the functionality of the standard's result set cursors.

T491, LATERAL derived tables

The Oracle TABLE operator in the FROM clause is equivalent to the LATERAL operator in the standard.

T571, Array-returning external SQL-invoked function

Oracle table functions returning a varray can be defined in external programming languages. When declaring such functions in SQL, use the CREATE FUNCTION command with the PIPELINED USING clause.

T571, Multiset-returning external SQL-invoked function

Oracle table functions returning a nested table can be defined in external programming languages. When declaring such functions in SQL, use the CREATE FUNCTION command with the PIPELINED USING clause.

T581, Regular expression substring functions

Oracle provides the REGEXP_SUBSTR function to perform substring operations using regular expression matching.

T613, Sampling

Oracle uses the keyword SAMPLE instead of the standard's keyword, TABLESAMPLE. Oracle uses the keyword BLOCK instead of the standard's keyword, SYSTEM. Oracle uses the absence of the keyword BLOCK to indicate a Bernoulli sampling of rows, indicated in the standard by the keyword BERNOULLI.

T652, SQL-dynamic statements in SQL routines

PL/SQL supports dynamic SQL.

T654, SQL-dynamic statements in external routines

Oracle supports dynamic SQL in embedded C, which may be used to create an external routine.

T655, Cyclically dependent routines

PL/SQL supports recursion.