Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 |
|
|
View PDF |
The following documents of the American National Standards Institute (ANSI) relate to SQL:
ANSI/ISO/IEC 9075-1:2003, Information technology—Database languages—SQL—Part 1: Framework (SQL/Framework)
ANSI/ISO/IEC 9075-2:2003, Information technology—Database languages—SQL—Part 2: Foundation (SQL/Foundation)
ANSI/ISO/IEC 9075-3:2003, Information technology—Database languages—SQL—Part 3: Call-Level Interface (SQL/CLI)
ANSI/ISO/IEC 9075-4:2003, Information technology—Database languages—SQL—Part 4: Persistent Stored Modules (SQL/PSM)
ANSI/ISO/IEC 9075-9:2003, Information technology—Database languages—SQL—Part 9: Management of External Data (SQL/MED)
ANSI/ISO/IEC 9075-10:2003, Information technology—Database languages—SQL—Part 10: Object Language Bindings (SQL/OLB)
ANSI/ISO/IEC 9075-11:2003, Information technology—Database languages—SQL—Part 11: Information and Definition Schemas (SQL/Schemata)
ANSI/ISO/IEC 9075-13:2003, Information technology—Database languages—SQL—Part 13: SQL Routines and Types using the Java Programming Language (SQL/JRT)
ANSI/ISO/IEC 9075-14:2003, Information technology—Database languages—SQL—Part 14: XML-Related Specifications (SQL/XML)
These standards are identical to the corresponding ISO standards listed in the next section.
You can obtain a copy of ANSI standards from this address:
or from their Web site:
http://webstore.ansi.org/ansidocstore/default.asp
A subset of ANSI standards, including the SQL standard, are INCITS standards. You can obtain these from the InterNational Committee for Information Technology Standards (INCITS) at:
http://www.incits.org/
The following documents of the International Organization for Standardization (ISO) relate to SQL:
ISO/IEC 9075-1:2003, Information technology—Database languages—SQL—Part 1: Framework (SQL/Framework)
ISO/IEC 9075-2:2003, Information technology—Database languages—SQL—Part 2: Foundation (SQL/Foundation)
ISO/IEC 9075-3:2003, Information technology—Database languages—SQL—Part 3: Call-Level Interface (SQL/CLI)
ISO/IEC 9075-4:2003, Information technology—Database languages—SQL—Part 4: Persistent Stored Modules (SQL/PSM)
ISO/IEC 9075-9:2003, Information technology—Database languages—SQL—Part 9: Management of External Data (SQL/MED)
ISO/IEC 9075-10:2003, Information technology—Database languages—SQL—Part 10: Object Language Bindings (SQL/OLB)
ISO/IEC 9075-11:2003, Information technology—Database languages—SQL—Part 11: Information and Definition Schemas (SQL/Schemata)
ISO/IEC 9075-13:2003, Information technology—Database languages—SQL—Part 13: SQL Routines and Types using the Java Programming Language (SQL/JRT)
ISO/IEC 9075-14:2003, Information technology—Database languages—SQL—Part 14: XML-Related Specifications (SQL/XML)
You can obtain a copy of ISO standards from this address:
or from their web store:
http://www.iso.ch/iso/en/prods-services/ISOstore/store.html
The ANSI and ISO SQL standards require conformance claims to state the type of conformance and the implemented facilities. The minimum claim of conformance is called Core SQL:2003 and is defined in Part 2, SQL/Foundation, and Part 11, SQL/Schemata, of the standard. The following products provide full or partial conformance with Core SQL:2003 as described in the tables that follow:
Oracle Database database server
Pro*C/C++, release 9.2.0
Pro*COBOL, release 9.2.0
Pro*Fortran, release 1.8.77
SQL Module for Ada (Mod*Ada), release 9.2.0
Pro*COBOL 1.8, release 1.8.77
Pro*PL/I, release 1.6.28
OTT, release 9.2.0.
OTT8, release 8.1.8
The Core SQL:2003 features that Oracle fully supports are listed in Table B-1:
Table B-1 Fully Supported Core SQL:2003 Features
Feature ID | Feature |
---|---|
E011 | Numeric data types |
E031 | Identifiers |
E061 | Basic predicates and search conditions |
E081 | Basic privileges |
E091 | Set functions |
E101 | Basic data manipulation |
E111 | Single row SELECT statement |
E131 | Null value support (nulls in lieu of values) |
E141 | Basic integrity constraints |
E151 | Transaction support |
E152 | Basic SET TRANSACTION statement |
E153 | Updatable queries with subqueries |
E161 | SQL comments using leading double minus |
E171 | SQLSTATE support |
F041 | Basic joined table |
F051 | Basic date and time |
F081 | UNION and EXCEPT in views |
F131 | Grouped operations |
F181 | Multiple module support |
F201 | CAST function |
F221 | Explicit defaults |
F261 | CASE expressions |
F311 | Schema definition statement |
F471 | Scalar subquery values |
F481 | Expanded NULL predicate |
The Core SQL:2003 features that Oracle partially supports are listed in Table B-2:
Table B-2 Partially Supported Core SQL:2003 Features
Feature ID, Feature | Partial Support |
---|---|
E021, Character data types | Oracle fully supports these subfeatures:
Oracle partially supports these subfeatures:
Oracle has equivalent functionality for these subfeatures:
|
E051, Basic query specification | Oracle fully supports the following subfeatures:
Oracle partially supports the following subfeatures:
Oracle does not support the following subfeature:
|
E071, Basic query expressions | Oracle fully supports the following subfeatures:
Oracle has equivalent functionality for the following subfeature:
|
E121, Basic cursor support | Oracle fully supports the following subfeatures:
Oracle partially supports the following subfeatures:
|
F031, Basic schema manipulation | Oracle fully supports these subfeatures:
Oracle partially supports this subfeature:
Oracle does not support these subfeatures (because Oracle does not support the keyword
|
F812, Basic flagging | Oracle has a flagger, but it flags SQL-92 compliance rather than SQL:2003 compliance |
T321, Basic SQL-invoked routines | Oracle fully supports these subfeatures:
Oracle supports these subfeatures with syntactic differences:
The Oracle syntax for
Oracle supports the following subfeatures in PL/SQL but not in Oracle SQL:
|
Oracle has equivalent functionality for the features listed in Table B-3:
Table B-3 Equivalent Functionality for Core SQL:2003 Features
Feature ID, Feature | Equivalent Functionality |
---|---|
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:
|
The Core SQL:2003 features that Oracle does not support are listed in Table B-4:
Table B-4 Unsupported Core SQL:2003 Features
Feature ID | Feature |
---|---|
F501 | Features and conformance views |
S011 | Distinct data types |
Note: Oracle does not support E182, Module language. Although this feature is listed in Table 35 in SQL/Foundation, it merely indicates that Core consists of a choice between Module language and embedded language. Module language and embedded language are completely equivalent in capability, differing only in the manner in which SQL statements are associated with the host programming language. Oracle supports embedded language. |
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 |
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 |
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. |
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 |
Oracle's MERGE statement is almost the same as the standard, with these exceptions:
|
F391, Long identifiers | Oracle supports identifiers up to 30 characters in length. |
F401, Extended joined table | Oracle supports FULL outer joins. |
F531, Temporary tables | Oracle supports GLOBAL TEMPORARY tables. |
F591, Derived tables | Oracle supports <derived table>, with the exception of:
|
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:
|
T271, Savepoints | Oracle supports this feature, except:
|
T331, Basic roles | Oracle supports this feature, except for REVOKE ADMIN OPTION FOR <role name>. |
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 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:
|
F381, Extended schema manipulation | Oracle has equivalent functionality for the following parts of this feature:
Oracle does not support the following parts of this feature:
|
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. |
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. |
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's VARRAY types are equivalent to array types in the standard. |
S092, Arrays of user-defined types | Oracle supports VARRAY s of object types. |
S094, Arrays of reference types | Oracle supports VARRAY s of references. |
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:
|
T131, Recursive query | Oracle's START WITH and CONNECT BY clauses can be used to perform many recursive queries |
T176, Sequence generator support | Oracle's sequences have the same capabilities as the standard's, though with different syntax. |
Oracle's PL/SQL provides functionality equivalent to SQL/PSM:2003, with minor syntactic differences, such as the spelling or arrangement of keywords.
The XML datatype in the standard is XML
. The Oracle equivalent datatype is XMLType
. We consider a feature of the standard to be fully supported if the only difference between Oracle and the standard is the spelling of the datatype name.
Table B-8 lists the XML features of the standard that are fully supported by Oracle.
Table B-8 Fully Supported Features of SQL/XML:2003
Feature ID | Feature |
---|---|
X010 | XML type |
X012 | Multisets of XML type |
X016 | Persistent XML values |
X020 | XML Concatenation |
X031 | XMLElement |
X032 | XMLForest |
X034 | XMLAgg |
X035 | XMLAgg: ORDER BY option |
X041 | Basic table mapping: null absent |
X042 | Basic table mapping null as nil |
X043 | Basic table mapping: table as forest |
X044 | Basic table mapping: table as element |
X045 | Basic table mapping: with target namespace |
X046 | Basic table mapping: data mapping |
X047 | Basic table mapping: metadata mapping |
X061 | XMLParse: CONTENT option |
X062 | XMLParse: DOCUMENT option |
X120 | XML parameters in SQL routines |
X121 | XML parameters in external routines |
Features X041 through X047, basic table mappings: Oracle table mappings are available through a Java interface and through a package. Oracle table mappings have been generalized to map queries and not just tables. To map only a table: SELECT * FROM
table_name
.
Features X061 and X062, XMLParse: These features are undocumented but are supported.
Table B-9 lists the features of SQL/XML:2003 that are supported through equivalent functionality in Oracle:
Table B-9 Equivalent Functionality for SQL/XML:2003 Features
Feature ID, Feature | Equivalent Functionality |
---|---|
X011, Arrays of XML Types | In Oracle, array types must be named, whereas in the standard they are anonymous. |
X014, Attributes of XML type | In Oracle, attributes of object types may be of type XMLType , but the syntax for creating object types is nonstandard. |
X070, XMLSerialize: CONTENT option |
In Oracle, serialization is done by invoking the GETCLOBVALUE method on an instance of XMLType . |
X080, Namespaces in XML publishing | In the Oracle implementation of XMLElement , XMLAttributes are used to define namespaces (XMLNamespaces is not implemented. |
X090, XML document predicate | In Oracle, you can test whether an XML value is a document by using the ISFRAGMENT method. |
Table B-10 lists the SQL/XML:2003 features that are not supported by Oracle.
Table B-10 Unsupported SQL/XML:2003 Features
Feature ID | Feature |
---|---|
X013 | Distinct types of XML |
X015 | Fields of XML type |
X033 | XMLRoot |
X051 | Advanced table mapping: null absent |
X052 | Advanced table mapping: null as nil |
X053 | Advanced table mapping: table as forest |
X054 | Advanced table mapping: table as element |
X055 | Advanced table mapping: with target namespace |
X056 | Advanced table mapping: data mapping |
X057 | Advanced table mapping: metadata mapping |
X081 | Query-level namespace declarations |
X082 | XML namespace declarations in DML |
X083 | XML namespace declarations in DDL |
X084 | XML namespace declarations in compound statements |
X100 | Host language support for XML: CONTENT option |
X101 | Host language support for XML: DOCUMENT option |
X110 | Host language support for XML: VARCHAR mapping |
X111 | Host language support for XML: CLOB mapping |
Oracle complied fully with last Federal Information Processing Standard (FIPS), which was FIPS PUB 127-2. That standard is no longer published. However, for users whose applications depend on information about the sizes of some database constructs that were defined in FIPS 127-2, we list the details of our compliance in Table B-11.
Table B-11 Sizing for Database Constructs
Database Constructs | FIPS | Oracle Database |
---|---|---|
Length of an identifier (in bytes) | 18 | 30 |
Length of CHARACTER datatype (in bytes) |
240 | 2000 |
Decimal precision of NUMERIC datatype |
15 | 38 |
Decimal precision of DECIMAL datatype |
15 | 38 |
Decimal precision of INTEGER datatype |
9 | 38 |
Decimal precision of SMALLINT datatype |
4 | 38 |
Binary precision of FLOAT datatype |
20 | 126 |
Binary precision of REAL datatype |
20 | 63 |
Binary precision of DOUBLE PRECISION datatype |
30 | 126 |
Columns in a table | 100 | 1000 |
Values in an INSERT statement |
100 | 1000 |
SET clauses in an UPDATE statement (Note 1) |
20 | 1000 |
Length of a row (Note2, Note 3) | 2,000 | 2,000,000 |
Columns in a UNIQUE constraint |
6 | 32 |
Length of a UNIQUE constraint (Note 2) |
120 | (Note 4) |
Length of foreign key column list (Note 2) | 120 | (Note 4) |
Columns in a GROUP BY clause |
6 | 255 (Note 5) |
Length of GROUP BY column list |
120 | (Note 5) |
Sort specifications in ORDER BY clause |
6 | 255 (Note 5) |
Length of ORDER BY column list |
120 | (Note 5) |
Columns in a referential integrity constraint | 6 | 32 |
Tables referenced in a SQL statement | 15 | No limit |
Cursors simultaneously open | 10 | (Note 6) |
Items in a select list | 100 | 1000 |
Note 1: The number of SET
clauses in an UPDATE
statement refers to the number items separated by commas following the SET
keyword.
Note 2: The FIPS PUB defines the length of a collection of columns to be the sum of: twice the number of columns, the length of each character column in bytes, decimal precision plus 1 of each exact numeric column, binary precision divided by 4 plus 1 of each approximate numeric column.
Note 3: The Oracle limit for the maximum row length is based on the maximum length of a row containing a LONG
value of length 2 gigabytes and 999 VARCHAR2
values, each of length 4000 bytes: 2(254) + 231 + (999(4000)).
Note 4: The Oracle limit for a UNIQUE
key is half the size of an Oracle data block (specified by the initialization parameter DB_BLOCK_SIZE
) minus some overhead.
Note 5: Oracle places no limit on the number of columns in a GROUP
BY
clause or the number of sort specifications in an ORDER
BY
clause. However, the sum of the sizes of all the expressions in either a GROUP
BY
clause or an ORDER
BY
clause is limited to the size of an Oracle data block (specified by the initialization parameter DB_BLOCK_SIZE
) minus some overhead.
Note 6: The Oracle limit for the number of cursors simultaneously opened is specified by the initialization parameter OPEN_CURSORS
. The maximum value of this parameter depends on the memory available on your operating system and exceeds 100 in all cases.
Oracle supports numerous features that extend beyond standard SQL. In your Oracle applications, you can use these extensions just as you can use Core SQL:2003.
If you are concerned with the portability of your applications to other implementations of SQL, use Oracle's FIPS Flagger to help identify the use of Oracle extensions to Entry SQL92 in your embedded SQL programs. The FIPS Flagger is part of the Oracle precompilers and the SQL*Module compiler.
See Also: Pro*COBOL Programmer's Guide and Pro*C/C++ Programmer's Guide for information on how to use the FIPS Flagger. |
Oracle supports most national, international, and vendor-specific encoded character set standards. A complete list of character sets supported by Oracle Appears in Appendix A, "Locale Data", in Oracle Database Globalization Support Guide.
Unicode is a universal encoded character set that lets you store information from any language using a single character set. Unicode is required by modern standards such as XML, Java, JavaScript, and LDAP. Unicode is compliant with ISO/IEC standard 10646. You can obtain a copy of ISO/IEC standard 10646 from this address:
Oracle Database complies fully with Unicode 3.0, the third and most recent version of the Unicode standard. For up-to-date information on this standard, visit the Web site of the Unicode Consortium:
http://www.unicode.org
Oracle uses UTF-8 (8-bit) encoding by way of three database character sets, two for ASCII-based platforms (UTF8 and AL32UTF8) and one for EBCDIC platforms (UTFE). If you prefer to implement Unicode support incrementally, you can store Unicode data in either the UTF-16 or UTF-8 encoding form, in the national character set, for the SQL NCHAR
datatypes (NCHAR
, NVARCHAR2
, and NCLOB
).