Oracle Support for Optional Features of SQL/Foundation:2003

Oracle supports the optional features of SQL/Foundation:2003 listed in Table B-5:

Table B-5 Fully Supported Optional Features of SQL/Foundation:2003

Feature ID	Feature
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

The optional features of SQL/Foundation:2003 that Oracle partially supports are listed in Table B-6:

Table B-6 Partially Supported Optional Features of SQL/Foundation:2003

Feature ID, Feature	Partial Support
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.
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.
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>.
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 this feature, except for <binary string literal> and the ability to specify an upper bound on the length of a LOB or CLOB.
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.
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.

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

Table B-7 Equivalent Functionality for Optional Features of SQL/Foundation:2003

Feature ID, Feature	Equivalent Functionality
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.
F033, `ALTER` `TABLE` statement: `DROP` `COLUMN` clause	Oracle provides a `DROP` `COLUMN` clause, but without the `RESTRICT` or `CASCADE` options found in the standard.
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
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.
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
S092, Arrays of user-defined types	Oracle supports `VARRAY`s of object types.
S094, Arrays of reference types	Oracle supports `VARRAY`s 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.
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, 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
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.
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.
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.
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`.