ANSI Standards

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:

American National Standards Institute

25 West 43rd Street

New York, NY 10036 USA

Telephone: +1.212.642.4900

Fax: +1.212.398.0023

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/

ISO Standards

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:

International Organization for Standardization

1 Rue de Varembé

Case postale 56

CH-1211, Geneva 20, Switzerland

Phone: +41.22.749.0111

Fax: +41.22.733.3430

Web site: http://www.iso.ch/

or from their web store:

http://www.iso.ch/iso/en/prods-services/ISOstore/store.html

Oracle Compliance To Core SQL:2003

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: E021-01, `CHARACTER` data type E021-07, Character concatenation E021-08, `UPPER` and `LOWER` functions E021-09, `TRIM` function E021-10, Implicit casting among character data types E021-12, Character comparison Oracle partially supports these subfeatures: E021-02, `CHARACTER` `VARYING` data type (Oracle does not distinguish a zero-length `VARCHAR` string from `NULL`) E021-03, Character literals (Oracle regards the zero-length literal `''` as being null) Oracle has equivalent functionality for these subfeatures: E021-04, `CHARACTER_LENGTH` function: use `LENGTH` function instead E021-05, `OCTET_LENGTH` function: use `LENGTHB` function instead E021-06, `SUBSTRING` function: use `SUBSTR` function instead E021-11, `POSITION` function: use `INSTR` function instead
E051, Basic query specification	Oracle fully supports the following subfeatures: E051-01, `SELECT` `DISTINCT` E051-02, `GROUP` `BY` clause E051-04, `GROUP` `BY` can contain columns not in <select list> E051-05, Select list items can be renamed E051-06, `HAVING` clause E051-07, Qualified * in select list Oracle partially supports the following subfeatures: E051-08, Correlation names in `FROM` clause (Oracle supports correlation names, but not the optional `AS` keyword) Oracle does not support the following subfeature: E051-09, Rename columns in the `FROM` clause
E071, Basic query expressions	Oracle fully supports the following subfeatures: E071-01, `UNION` `DISTINCT` table operator E071-02, `UNION` `ALL` able operator E071-05, Columns combined by table operators need not have exactly the same type E071-06, table operators in subqueries Oracle has equivalent functionality for the following subfeature: E071-03, `EXCEPT` `DISTINCT` table operator: Use `MINUS` instead of `EXCEPT` `DISTINCT`
E121, Basic cursor support	Oracle fully supports the following subfeatures: E121-01, `DECLARE` `CURSOR` E121-02, `ORDER` `BY` columns need not be in select list E121-03, Value expressions in `ORDER` `BY` clause E121-04, `OPEN` statement E121-06, Positioned `UPDATE` statement E121-07, Positioned `DELETE` statement E121-08, `CLOSE` statement E121-10, `FETCH` statement, implicit `NEXT` Oracle partially supports the following subfeatures: E121-17, `WITH` `HOLD` cursors (in the standard, a cursor is not held through a `ROLLBACK`, but Oracle does hold through `ROLLBACK`)
F031, Basic schema manipulation	Oracle fully supports these subfeatures: F031-01, `CREATE` `TABLE` statement to create persistent base tables F031-02, `CREATE` `VIEW` statement F031-03, `GRANT` statement Oracle partially supports this subfeature: F031-04, `ALTER` `TABLE` statement: `ADD` `COLUMN` clause (Oracle does not support the optional keyword `COLUMN` in this syntax) Oracle does not support these subfeatures (because Oracle does not support the keyword `RESTRICT`): F031-13, `DROP` `TABLE` statement: `RESTRICT` clause F031-16, `DROP` `VIEW` statement: `RESTRICT` clause F031-19, `REVOKE` statement: `RESTRICT` clause
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: T321-03, function invocation T321-04, `CALL` statement Oracle supports these subfeatures with syntactic differences: T321-01, user-defined functions with no overloading T321-02, user-defined procedures with no overloading The Oracle syntax for `CREATE` `FUNCTION` and `CREATE` `PROCEDURE` differs from the standard as follows: In the standard, the mode of a parameter (`IN`, `OUT` or `INOUT`) comes before the parameter name, whereas in Oracle it comes after the parameter name. The standard uses `INOUT`, whereas Oracle uses `IN` `OUT`. Oracle requires either `IS` or `AS` after the return type and before the definition of the routine body, while the standard lacks these keywords. If the routine body is in C (for example), then the standard uses the keywords `LANGUAGE` `C` `EXTERNAL` `NAME` to name the routine, whereas Oracle uses `LANGUAGE` `C` `NAME`. If the routine body is in SQL, then Oracle uses its proprietary procedural extension called PL/SQL. Oracle supports the following subfeatures in PL/SQL but not in Oracle SQL: T321-05, `RETURN` statement

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

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: Instead of `TABLES`, use `ALL_TABLES`. Instead of `COLUMNS`, use `ALL_TAB_COLUMNS`. Instead of `VIEWS`, use `ALL_VIEWS`. However, Oracle's `ALL_VIEWS` does not display whether a user view was defined `WITH` `CHECK` `OPTION` or if it is updatable. To see whether a view has `WITH` `CHECK` `OPTION`, use `ALL_CONSTRAINTS`, with `TABLE_NAME` equal to the view name and look for `CONSTRAINT_TYPE` equal to `'V'`. Instead of `TABLE_CONSTRAINTS`, `REFERENTIAL_CONSTRAINTS` and `CHECK_CONSTRAINTS`, use `ALL_CONSTRAINTS`. However, Oracle's `ALL_CONSTRAINTS` does not display whether a constraint is deferrable or initially deferred.

Oracle does not have any of the views in this feature. However, Oracle makes the same information available in other metadata views:

Instead of TABLES, use ALL_TABLES.
Instead of COLUMNS, use ALL_TAB_COLUMNS.
Instead of VIEWS, use ALL_VIEWS.

However, Oracle's ALL_VIEWS does not display whether a user view was defined WITH CHECK OPTION or if it is updatable. To see whether a view has WITH CHECK OPTION, use ALL_CONSTRAINTS, with TABLE_NAME equal to the view name and look for CONSTRAINT_TYPE equal to 'V'.
Instead of TABLE_CONSTRAINTS, REFERENTIAL_CONSTRAINTS and CHECK_CONSTRAINTS, use ALL_CONSTRAINTS.

However, Oracle's ALL_CONSTRAINTS does not display whether a constraint is deferrable or initially deferred.

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 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
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: 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	Oracle's `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 <override clause>
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: 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's before row and before-statement triggers may perform DML statements, which is forbidden in the standard. On the other hand, Oracle's after-row statements may not perform DML, while it is permitted in the standard. When multiple triggers apply, the standard says they are executed in order of definition; in 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>.

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 wish 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`.
F381, Extended schema manipulation	Oracle has equivalent functionality for the following parts of this feature: <alter column definition>: use the `MODIFY` keyword (instead of `ALTER`) in an `ALTER` `TABLE` statement to alter a column. Oracle does not support the following parts of this feature: <drop schema statement> <alter routine statement>
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: 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.
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 Compliance with SQL/CLI:2003

Oracle's ODBC driver conforms to SQL/CLI:2003.

Oracle Compliance with SQL/PSM:2003

Oracle's PL/SQL provides functionality equivalent to SQL/PSM:2003, with minor syntactic differences, such as the spelling or arrangement of keywords.

Oracle Compliance with SQL/MED:2003

Oracle does not comply with SQL/MED:2003.

Oracle Compliance with SQL/XML:2003

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

Notes to Table B-8:

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 Compliance with FIPS 127-2

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 Extensions to Standard SQL

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.

Character Set Support

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:

International Organization for Standardization

1 Rue de Varembé

Case postale 56

CH-1211, Geneva 20, Switzerland

Phone: +41.22.749.0111

Fax: +41.22.733.3430

Web site: http://www.iso.ch/

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).

See Also:

Oracle Database Globalization Support Guide for details on Oracle character set support.