Oracle® Database SQL Language Reference 11g Release 2 (11.2) Part Number E17118-04 |
|
|
PDF · Mobi · ePub |
Oracle's support for optional features of SQL/Foundation:2008 is listed in Table C-2:
Table C-2 Oracle Support for Optional Features of SQL/Foundation:2008
Feature ID, Feature | Support |
---|---|
B012, Embedded C |
Oracle fully supports this feature. |
B013, Embedded COBOL |
Oracle fully supports this feature. |
B014, Embedded Fortran |
Oracle fully supports this feature. |
B021, Direct SQL |
Oracle fully supports this feature, as SQL*Plus. |
B031, Basic dynamic SQL |
Oracle supports dynamic SQL in two styles, documented in the embedded language manuals as "Oracle dynamic SQL" and "ANSI dynamic SQL." ANSI dynamic SQL is an implementation of the standard, with the following restrictions:
Oracle dynamic SQL is similar to standard dynamic SQL, with the following modifications:
|
B032, Extended dynamic SQL |
In ANSI 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. In Oracle dynamic SQL, Oracle's |
B111, Module language Ada |
Oracle fully supports 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. |
B128, Routine language SQL |
Oracle supports routines written in PL/SQL, which is Oracle's equivalent to the standard procedural language SQL/PSM. |
F032, |
In Oracle, a |
F033, |
Oracle provides a |
F034, Extended |
Oracle supports the following parts of this feature:
Oracle provides equivalent functionality for the following parts of this feature:
|
F052, Intervals and datetime arithmetic |
Oracle only supports the |
F111, Isolations levels other than |
In addition to |
F121, Basic diagnostics management |
Much of the functionality of this feature is provided through the SQLCA in embedded languages. |
F191, Referential delete actions |
Oracle supports |
F200, |
Oracle fully supports this feature, and extends it by permitting truncation of a table that references itself in a referential integrity constraint. |
F231, Privilege tables |
Oracle makes this information available in the following metadata views:
|
F281, |
Oracle fully supports this feature. |
F291, |
The UNIQUE <table subquery> is CAST (<table subquery> AS MULTISET) IS A SET |
F302, |
Oracle supports |
F312, |
The Oracle
|
F341, Usage tables |
Oracle makes this information available in the views |
F381, Extended schema manipulation |
Oracle fully supports the following element of this feature:
Oracle partially supports the following element of this feature:
Oracle provides equivalent functionality for the following element of this feature:
Oracle does not support the following parts of this feature:
|
F382, Alter column data type |
Oracle supports this functionality, though with non-standard syntax. |
F391, Long identifiers |
Oracle supports identifiers up to 30 characters in length. |
F393, Unicode escapes in literals |
The Oracle |
F394, Optional normal form specification |
This feature adds the keywords
Oracle does not support the |
F401, Extended joined table |
Oracle supports |
F402, Named 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. |
F403, Partitioned join tables |
Oracle supports this feature, except with |
F411, Time zone specification |
Oracle fully supports |
F421, National character |
Oracle fully supports this feature. |
F441, Extended set function support |
Oracle supports the following parts of this feature:
|
F442, Mixed column references in set functions |
Oracle fully supports this feature. |
F461, Named character sets |
Oracle supports many character sets with Oracle-defined names. Oracle does not support any other aspect of this feature. |
F491, Constraint management |
Oracle fully supports this feature. |
F531, Temporary tables |
Oracle supports |
F555, Enhanced seconds precision |
Oracle provides enhanced support for this feature, supporting up to 9 places after the decimal point. |
F561, Full value expressions |
Oracle fully supports this feature. |
F571, Truth value tests |
Oracle's |
F591, Derived tables |
Oracle supports <derived table>, with the exception of:
|
F641, Row and table constructors |
In Oracle, a row constructor may be used in an equality or inequality comparison with another row constructor or with a subquery. Oracle does not support anything else in this feature. |
F690, Collation support |
Oracle's |
F693, SQL-sessions and client module collations |
To set a session collation, use |
F695, Translation support |
The Oracle |
F721, Deferrable constraints |
Oracle fully supports this feature. |
F731, |
Oracle fully supports this feature. |
F761, Session management |
Oracle provides the following equivalents for elements of this feature:
|
F771, Connection management |
Oracle's |
F781, Self-referencing operations |
Oracle fully supports this feature. |
F801, Full set function |
Oracle fully supports this feature. |
F831, Full cursor update |
Oracle supports the combination of |
F841, |
Oracle's equivalent is |
F842, |
Oracle's equivalent is |
F843, |
Oracle's equivalent is |
F844, |
Oracle's equivalent is |
F845, |
Oracle's equivalent is |
S023, Basic structured types |
Oracle's object types are equivalent to structured types in the standard. |
S024, Enhanced structured types |
Oracle's syntax is non-standard, but provides equivalents for the following:
|
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. To dereference a reference, dot notation is used, instead of -> as in the standard. |
S043, Enhanced reference types |
Oracle supports the following elements of this feature:
|
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, create a hierarchy of views on those base tables. |
S091, Array types |
Oracle
|
S092, Arrays of user-defined types |
Oracle supports |
S094, Arrays of reference types |
Oracle supports |
S095, Array constructors by query |
Oracle supports this using |
S097, Array element assignment |
In PL/SQL, you can assign to array elements, using syntax that is similar to the standard (SQL/PSM). |
S111, |
Oracle supports the |
S151, Type predicate |
Oracle fully supports this feature. |
S161, Subtype treatment |
Supported, with a minor syntactic difference: The standard requires parentheses around the referenced type's name; Oracle does not support parentheses in this position. |
S162, Subtype treatment for references |
The standard requires parentheses around the referenced type's name; Oracle does not support parentheses in this position. |
S201, SQL-invoked routines on arrays |
PL/SQL provides the ability to pass arrays as parameters and return arrays as the result of functions. Procedures and functions written in C may pass arrays and return arrays as the result of functions using the Oracle Type Translator (OTT). |
S202, SQL-invoked routines on multisets |
A PL/SQL routine may have nested tables as parameters, and may return a nested table. Routines written in C may pass arrays and return arrays as the result of functions using the Oracle Type Translator. |
S232, Array locators |
Oracle Type Translator supports descriptors for arrays, which achieve the same purpose as locators. |
S233, Multiset locators |
Oracle supports locators for nested tables. |
S241, Transform functions |
The Oracle Type Translator provides the same capability as transforms. |
S251, User-defined orderings |
Oracle's object type ordering capabilities correspond to the standard's capabilities as follows:
|
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 Oracle supports the following elements of this feature on nested tables using the same syntax as the standard has for multisets:
All other aspects of this feature are supported with non-standard syntax, as follows:
|
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:
Oracle does not support the |
S281, Nested collection types |
Oracle permits nesting of its collection types (varray and nested table). |
T041, Basic LOB data type support |
Oracle supports the following aspects of this feature:
Oracle provides equivalent support for the following aspects of this feature:
Oracle does not support the following aspects of this feature:
|
T042, Extended LOB support |
Oracle fully supports the following element of this feature:
Oracle provides equivalent functionality for the following elements of this feature:
The following elements of this feature are not supported:
|
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 does not support the |
T071, |
On many implementations, |
T111, Updatable joins, unions and columns |
Oracle's updatable join views are a subset of the standard's updatable join capabilities. |
T121, |
Oracle fully supports this feature. |
T122, |
Oracle fully supports this feature. |
T131, Recursive query |
Oracle supports the use of a |
T132, Recursive query in subquery |
Oracle supports the use of a |
T141, |
Oracle provides |
T172, |
Oracle's |
T175, Generated columns |
Oracle supports this feature, with the following restrictions:
|
T176, Sequence generator support |
Oracle's sequences have the same capabilities as the standard's, though with different syntax. |
T201, Comparable data types for referential constraints |
Oracle fully supports this feature. |
T211, Basic trigger capability |
Oracle's triggers differ from the standard as follows:
|
T212, Enhanced trigger capability |
This feature permits statements triggers, which Oracle supports, as described for feature T211, Basic trigger capability. |
T213, |
Oracle supports |
T241, |
Oracle's |
T271, Savepoints |
Oracle supports this feature, except:
|
T285, Enhanced derived column names |
This feature pertains only to derived columns in a |
T322, Overloading of SQL-invoked functions and procedures |
Oracle supports overloading of functions and procedures. However, the rules for handling certain data type 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 |
T324, Explicit security for SQL routines |
Oracle's syntax |
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:
|
T331, Basic roles |
Oracle supports this feature, except for |
T351, Bracketed comments |
Oracle fully supports this feature. |
T431, Extended grouping capabilities |
Oracle fully supports this feature. |
T432, Nested and concatenated |
Oracle supports concatenated |
T433, Multiargument function |
The Oracle |
T441, |
Oracle supports the |
T471, Result sets return value |
PL/SQL ref cursors provide all the functionality of the standard's result set cursors. |
T491, |
The Oracle |
T501, Enhanced |
Oracle fully supports this feature. |
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 |
T572, 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 |
T581, Regular expressions substring functions |
Oracle provides the |
T591, |
Oracle permits a |
T611, Elementary OLAP operations |
Oracle fully supports this feature. |
T612, Advanced OLAP operations |
Oracle supports the following elements of this feature: Oracle does not support the following elements of this feature:
|
T613, Sampling |
Oracle uses the keyword |
T621, Enhanced numeric functions |
Oracle fully supports this feature, except for the alternate spelling |
T641, Multiple column assignment |
The standard syntax to assign to multiple columns is supported if the assignment source is a subquery. |
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. |