This appendix discusses Oracle's conformance with the SQL:1999 standards. The mandatory portion of SQL:1999 is known as Core SQL:1999 and is found in SQL:1999 Part 2 (Foundation) and Part 5 (Bindings). The Foundation features are analyzed in Annex F of Part 2 in the table "SQL/Foundation feature taxonomy and definition for Core SQL". The Bindings features are analyzed in Annex F of Part 5 in the table "SQL/Bindings feature taxonomy and definition for Core SQL".

This appendix declares Oracle's conformance to the SQL standards established by the American National Standards Institute (ANSI) and the International Standards Organization (ISO). (The ANSI and ISO SQL standards are identical.) It contains the following sections:

Oracle Compliance

The ANSI and ISO SQL standards require conformance claims to state the type of conformance and the implemented facilities. The Oracle9i server, Oracle Precompilers for C/C++ release 8.1, Oracle Precompiler for Cobol release 8.1, and SQL*Module for ADA release 8.0.4 provide full or partial conformance with the ANSI and ISO standards as described in the tables that follow.

The Core SQL:1999 features that Oracle fully supports are listed in Table B-1:

Table B-1 Fully Supported Core SQL:1999 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 SETTRANSACTION 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

B011

Embedded Ada

B012

Embedded C

B013

Embedded COBOL

B014

Embedded Fortran

T431

Extended grouping capabilities

T611

Elementary OLAP operators

T621

Enhanced numeric functions

The Core SQL:1999 features that Oracle partially supports are listed in Table B-2:

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

Table B-3 Equivalent Functionality for Core SQL:1999 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:

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 WITHCHECKOPTION or if it is updatable. To see whether a view has WITHCHECKOPTION, 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:1999 features that Oracle does not support are listed in Table B-4:

Table B-4 Unsupported Core SQL:1999 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 31 in the standard, 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.

FIPS Compliance

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

Table B-5 Sizing for Database Constructs

Database Constructs

FIPS

Oracle9i

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(a)

20

1000

Length of a row(b,c)

2,000

2,000,000

Columns in a UNIQUE constraint

6

32

Length of a UNIQUE constraint(b)

120

(d)

Length of foreign key column list(b)

120

(d)

Columns in a GROUP BY clause

6

255(e)

Length of GROUP BY column list

120

(e)

Sort specifications in ORDER BY clause

6

255(e)

Length of ORDER BY column list

120

(e)

Columns in a referential integrity constraint

6

32

Tables referenced in a SQL statement

15

No limit

Cursors simultaneously open

10

(f)

Items in a SELECT list

100

1000

(a) The number of SET clauses in an UPDATE statement refers to the number items separated by commas following the SET keyword.(b) 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. (c) 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)).(d) 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. (e) 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. (f) 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:1999.

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.

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 Oracle9i 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, LDAP, CORBA 3.0. 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/

Oracle9i 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:

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