B
Oracle and Standard SQL

High thoughts must have high language.

Aristophanes, Frogs

This appendix discusses Oracle's conformance to the SQL standards established by industry standards governing bodies. It also described how to locate extensions to standard SQL with the FIPS Flagger.

Conformance with Standard SQL

This section declares Oracle's conformance to the SQL standards established by these organizations:

American National Standards Institute (ANSI)
International Standards Organization (ISO)
United States Federal Government

ANSI and ISO Compliance

Oracle8i complies at the Entry level as defined in the ANSI document, X3.135-1992, "Database Language SQL." You can obtain a copy of the ANSI standard from this address:

American National Standards Institute
1430 Broadway
New York, NY 10018 USA

The ANSI and ISO SQL standards require conformance claims to state the type of conformance and the implemented facilities. The Oracle 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 conformance with the ANSI X3.135-1992/ISO 9075-1992 standard:

Compliance at Entry Level (including both SQL-DDL and SQL-DML)
Module Language for ADA
Embedded SQL C
Embedded SQL COBOL

In addition to full compliance at the Entry level, Oracle complies partially at the Transitional, Intermediate, and Full levels as described in Table B-1 (including both SQL-DDL and SQL-DML).

Table B-1 Oracle Compliance at Transitional, Intermediate, and Full Levels

Level	SQL92 Feature (number and name)
Transitional	7. TRIM function
	8. UNION in views
	9. Implicit numeric casting
	10. Implicit character casting
	13. Grouped operations
	14. Qualified * in SELECT list
	15. Lowercase identifiers
	16. PRIMARY KEY enhancement
	18. Multiple module support
	21. INSERT expressions
Intermediate	31. Schema definition statement
	42. National character
	48. Expanded null predicate
Full	60. Trailing underscore
	62. Referential name order

FIPS Compliance

Oracle complies completely with FIPS PUB 127-2 for Entry SQL. In addition, the following information is provided for Section 16, "Special Procurement Considerations."

Section 16.2 Programming Language Interfaces

The Oracle precompilers support the use of embedded SQL in C and COBOL. SQL*Module supports the use of Module Language in ADA.

Section 16.3 Style of Language Interface

Oracle with SQL*Module supports Module Language for Ada. Oracle with the Oracle precompilers supports C and COBOL. The specific languages supported depend on your operating system.

Section 16.5 Interactive Direct SQL

Oracle8i with SQL*Plus Version 3.1 (as well as other Oracle tools) supports "direct invocation" of the following SQL statements, meeting the requirements of FIPS PUB 127-2:

CREATE TABLE statement
CREATE VIEW statement
GRANT statement
INSERT statement
SELECT statement, with ORDER BY clause but not INTO clause
UPDATE statement: searched
DELETE statement: searched
COMMIT WORK statement
ROLLBACK WORK statement

Most other SQL statements described in this reference are also supported interactively.

Section 16.6 Sizing for Database Constructs

Table B-2 lists requirements identified in FIPS PUB 127-1 and how they are met by Oracle8i.

Table B-2 Sizing for Database Constructs

Database Constructs	FIPS	Oracle8i
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.

Section 16.7 Character Set Support

Oracle supports the ASCII character set (FIPS PUB 1-2) on most computers and the EBCDIC character set on IBM mainframe computers. Oracle supports both single-byte and multibyte character sets.

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 Entry SQL92.

If you are concerned with the portability of your applications to other implementations of SQL, use Oracle's FIPS Flagger to locate Oracle extensions to Entry SQL92 in your embedded SQL programs. The FIPS Flagger is part of the Oracle precompilers and the SQL*Module compiler. For information on how to use the FIPS Flagger, see Pro*COBOL Precompiler Programmer's Guide and Pro*C/C++ Precompiler Programmer's Guide.

B Oracle and Standard SQL