Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

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

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

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

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/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 following products 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
  • 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 does not support 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

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

Notes to Table B-5:

(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 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/

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