B
Oracle and Standard SQL

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:

• ANSI Standards

• ISO Standards

• Oracle Compliance

• FIPS Compliance

ANSI Standards

The following documents of the American National Standards Institute (ANSI) relate to SQL:

• ANSI/ISO/IEC 9075-1:1999, Information technology--Database languages--SQL--Part 1: Framework (SQL/Framework)
  • ANSI/ISO/IEC 9075-1:1999/Amd.1:2000

• ANSI/ISO/IEC 9075-2:1999, Information technology--Database languages--SQL--Part 2: Foundation (SQL/Foundation)

• ANSI/ISO/IEC 9075-5:1999, Information technology--Database languages--SQL--Part 5: Host Language Bindings (SQL/Bindings)

You can obtain a copy of ANSI standards from this address:

American National Standards Institute

11 West 42nd Street

New York, NY 10036 USA

Telephone: [phone]

Fax: [phone]

or from their web site:

http://webstore.ansi.org/ansidocstore/default.asp

A subset of ANSI standards, including the SQL standard, are X3 or NCITS standards. You can obtain these from the National Committee for Information Technology Standards (NCITS) at:

http://www.cssinfo.com/ncitsgate.html

ISO Standards

The following documents of the International Organization for Standardization (ISO) relate to SQL:

• ISO/IEC 9075-1:1999, Information technology--Database languages--SQL--Part 1: Framework (SQL/Framework)
  • ISO/IEC 9075-1:1999/Amd.1:2000

• ISO/IEC 9075-2:1999, Information technology--Database languages--SQL--Part 2: Foundation (SQL/Foundation)

• ISO/IEC 9075-5:1999, Information technology--Database languages--SQL--Part 5: Host Language Bindings (SQL/Bindings)

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: [phone]

Fax: [phone]

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

or from their web store:

http://www.iso.ch/cate/cat.html

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

Table B-2 Partially Supported Core SQL:1999 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-11, 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
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
E051, Basic query specification	Oracle fully supports the following subfeatures: E051-01, SELECT DISTINCT E05102, GROUP BY clause E051-04, GROUP BY can contain columns not in <select list> E051-06, HAVING clause E051-07, Qualified * in select list Oracle partially supports the following subfeatures: E051-05, Select list items can be renamed (Oracle supports column aliases, but not the optional AS keyword) 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)
F812, Basic flagging	Oracle has a flagger, but it flags SQL-92 compliance rather than SQL:1999 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
T612, Advanced OLAP operators	Oracle fully supports the following subfeatures: T612.b: WIDTH_BUCKET function T612.c.ii: PERCENT_RANK and CUME_DIST functions T612.f.i: Hypothetical set functions and inverse distribution functions

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

See Also: Pro*COBOL Precompiler Programmer's Guide and Pro*C/C++ Precompiler 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 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: [phone]

Fax: [phone]

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:

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: Oracle9i Globalization Support Guide for details on Oracle character set support.