Oracle® Database SQL Language Reference 11g Release 1 (11.1) Part Number B28286-01 |
|
|
View PDF |
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 |
|
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 |
|
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 |
|
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:
|
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 |
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 |
F191, Referential delete actions |
Oracle supports |
F302, |
Oracle supports |
F312, |
The Oracle
|
F391, Long identifiers |
Oracle supports identifiers up to 30 characters in length. |
F401, Extended joined table |
Oracle supports |
F403, Partitioned join tables |
Oracle supports this feature, except with |
F441, Extended set function support |
Oracle supports the following parts of 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. |
F531, Temporary tables |
Oracle supports |
F591, Derived tables |
Oracle supports <derived table>, with the exception of:
|
F831, Full cursor update |
Oracle supports the combination of |
S111, |
Oracle supports the |
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:
Oracle provides equivalent support for the following aspects of this feature:
Oracle does not support the following aspects of this feature:
|
T111, Updatable joins, unions and columns |
Oracle's updatable join views are a subset of the standard's updatable join capabilities. |
T121, |
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, |
Same restriction as Feature T121. |
T211, Basic trigger capability |
Oracle's triggers differ from the standard as follows:
|
T271, Savepoints |
Oracle supports this feature, except:
|
T331, Basic roles |
Oracle supports this feature, except for |
T432, Nested and concatenated |
Oracle supports concatenated |
T591, |
Oracle permits a |
T612, Advanced OLAP operations |
Oracle supports the following elements of this feature: Oracle does not support the following elements of this feature:
|
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:
|
B032, Extended dynamic SQL |
Oracle's |
B122, Routine language C |
Oracle supports external routines written in C, though Oracle does not support the standard syntax for creating such routines. |
F032, |
In Oracle, a |
F033, |
Oracle provides a |
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:
|
F341, Usage tables |
Oracle makes this information available in the views |
F381, Extended schema manipulation |
Oracle fully supports the following elements of this feature:
Oracle partially supports the following elements of this feature:
Oracle provides equivalent functionality for the following elements of this feature:
Oracle does not support the following parts of this feature:
|
F93, Unicode escapes in literals |
The Oracle |
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 |
F690, Collation support |
Oracle provides functions that may be used to change the collation of character expressions. |
F695, Translation support |
Oracle's |
F771, Connection management |
Oracle's |
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
|
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). |
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:
|
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) |
T042, Extended LOB support |
Oracle fully supports the following elements 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, |
T131, Recursive query |
Oracle's |
T132, Recursive query in subquery |
Oracle's |
T141, |
Oracle provides |
T172, |
Oracle's |
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 |
T324, Explicit security for external 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:
|
T433, Multiargument function |
The Oracle |
T471, Result sets return value |
PL/SQL ref cursors provide all the functionality of the standard's result set cursors. |
T491, |
The Oracle |
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 |
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 |
T581, Regular expression substring functions |
Oracle provides the |
T613, Sampling |
Oracle uses the keyword |
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. |