B
Oracle and Standard SQL

This appendix discusses the following topics:

• Oracle's conformance to the SQL standards established by industry standards governing bodies
• Oracle's extensions to standard SQL
• Locating 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

Conformance with these standards is measured by the National Institute of Standards and Technology (NIST) "SQL Test Suite". NIST is an organization of the government of the United States of America.

ANSI and ISO Compliance

Oracle8 conforms to Entry level conformance 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 Oracle8 server, the Oracle Precompiler for Fortran Version 1.8.25, Oracle Precompilers for C/C++ Version 8.0.4, Oracle Precompiler for Cobol Version 8.0.4, and SQL*Module for ADA Version 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
• Embedded SQL FORTRAN

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, COBOL, and Fortran. 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, COBOL, and FORTRAN. The specific languages supported depend on your operating system.

Section 16.5 Interactive Direct SQL

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

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

Most other SQL commands described in this guide are also supported interactively.

Section 16.6 Sizing for Database Constructs

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

Table B-1 Sizing for Database Constructs

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

Extensions to Standard SQL

This section lists the additional features supported by Oracle that extend beyond standard SQL "Database Language SQL". This section provides information on these parts of the SQL language:

• commands
• functions
• operators
• pseudocolumns
• datatypes
• names of schema objects
• values

For information on the extensions to standard embedded SQL "Database Language Embedded SQL" supported by the Oracle Precompilers, see Pro*COBOL Precompiler Programmer's Guide, Pro*C/C++ Precompiler Programmer's Guide, and SQL*Module for Ada Programmer's Guide.

Commands

This section describes these additional commands and additional syntax and functionality of standard commands. Oracle supports these commands that are not part of Entry SQL92:

ALTER CLUSTER ALTER DATABASE ALTER FUNCTION ALTER INDEX ALTER PACKAGE ALTER PROCEDURE ALTER PROFILE ALTER RESOURCE COST ALTER ROLLBACK SEGMENT ALTER ROLE ALTER SEQUENCE ALTER SESSION ALTER SNAPSHOT ALTER SNAPSHOT LOG ALTER SYSTEM ALTER TABLE ALTER TABLESPACE ALTER TRIGGER ALTER TYPE ALTER USER ALTER VIEW ANALYZE AUDIT COMMENT CREATE CONTROLFILE CREATE CLUSTER CREATE DATABASE CREATE DATABASE LINK CREATE DIRECTORY CREATE FUNCTION CREATE INDEX CREATE LIBRARY CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE CREATE PROFILE CREATE ROLLBACK SEGMENT CREATE ROLE

CREATE SEQUENCE CREATE SNAPSHOT CREATE SNAPSHOT LOG CREATE SYNONYM CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE TYPE CREATE TYPE BODY CREATE USER CREATE VIEW DROP CLUSTER DROP DATABASE LINK DROP DIRECTORY DROP FUNCTION DROP INDEX DROP LIBRARY DROP PACKAGE DROP PROCEDURE DROP PROFILE DROP ROLLBACK SEGMENT DROP ROLE DROP SEQUENCE DROP SNAPSHOT DROP SNAPSHOT LOG DROP SYNONYM DROP TABLE DROP TABLESPACE DROP TYPE DROP TYPE BODY EXPLAIN PLAN NOAUDIT RENAME REVOKE SAVEPOINT SET CONSTRAINT SET TRANSACTION TRUNCATE

Additional Parts of Standard Commands

Oracle supports additional syntax for some commands that are part of Entry SQL92.

COMMIT

The COMMIT command supports these additional clauses:

• COMMENT clause
• FORCE clause

Also, Entry SQL92 requires a COMMIT statement to include the WORK keyword. Oracle allows your COMMIT statements to either include or omit this keyword. This keyword adds no functionality to the command.

CREATE TABLE

The CREATE TABLE command supports these additional parameters and clauses:

• AS clause
• ENABLE clause
• DISABLE clause
• CLUSTER clause
• INITRANS parameter
• MAXTRANS parameter
• ORGANIZATION clause
• PCTFREE parameter
• PCTTHRESHOLD parameter
• PCTUSED parameter
• STORAGE clause
• TABLESPACE parameter

CONSTRAINT Clause The CONSTRAINT clause of the CREATE TABLE command supports these additional options and identifiers:

• ON DELETE CASCADE option
• ENABLE option
• DISABLE option
• CONSTRAINT identifier

Column definitions in a CREATE TABLE command support these additional clauses:

• WITH ROWID
• SCOPE

In addition, columns may be defined using any Oracle predefined type, not just the Entry SQL92 datatypes. Oracle's extended datatypes are noted below. If a column's datatype is BLOB, CLOB, or NCLOB, then special LOB storage and index features can be specified in a CREATE TABLE command.

CREATE VIEW

The CREATE VIEW command supports this additional syntax:

• OR REPLACE option
• FORCE and NOFORCE options
• CONSTRAINT identifier with the WITH CHECK OPTION

If you omit column names from a CREATE VIEW statement, the column aliases that appear in the defining query are used for columns of the view.

DELETE

The DELETE command supports this additional syntax:

• Database links to delete rows from tables and views on remote databases
• Table aliases for use with correlated queries
• PARTITION clause
• RETURNING clause

Also, Entry SQL92 requires a DELETE statement to include the FROM keyword. Oracle allows your DELETE statements to either include or omit this keyword. Note that this keyword adds no functionality to the command.

Oracle allows a DELETE command against a modifiable join view with exactly one key-preserved table in the join; SQL92 does not allow DELETE against a join view.

GRANT

The GRANT command (System Privileges and Roles) is an extension to standard SQL.

The GRANT command (Object Privileges) supports the following other privileges on other objects in addition to the DELETE, INSERT, REFERENCES, SELECT, and UPDATE privileges on tables and views supported by Entry SQL92:

• ALTER
• EXECUTE
• INDEX
• READ

This command also supports granting object privileges to roles.

INSERT

The INSERT command supports the use of database links to insert rows into tables and views on remote databases. The INSERT command supports this additional syntax:

• PARTITION clause
• RETURNING clause

The INSERT command supports a subquery in the INTO clause, similar to inserting into a view.

The INSERT command can insert into a modifiable join view that does not specify the WITH CHECK OPTION provided that all columns to be inserted are in the same key-preserved table of the join.

ROLLBACK

The ROLLBACK command supports these additional clauses:

• TO clause
• FORCE clause

Also, Entry SQL92 requires a ROLLBACK statement to include the WORK keyword. Oracle allows your ROLLBACK statements to either include or omit this keyword. This keyword adds no functionality to the command.

SELECT

The SELECT command supports these additional clauses and syntax:

• START WITH clause
• CONNECT BY clause
• FOR UPDATE clause
• Database links for querying tables, views, and snapshots on remote databases
• Outer join operator (+) for performing outer joins
• NULL in the select list

GROUP BY Clause The GROUP BY clause of the SELECT command supports this additional syntax and functionality:

• A SELECT statement that selects from a view whose defining query contains group functions or a GROUP BY clause can contain group functions and GROUP BY, HAVING, and WHERE clauses.
• A SELECT statement can perform a join involving a view whose defining query contains a GROUP BY clause.

ORDER BY Clause The ORDER BY clause of the SELECT command supports this additional syntax and functionality:

• This clause can also specify any expression involving any columns in any tables or views that appear in the FROM clause, rather than only names, aliases, or position numbers of columns in the select list.
• This clause can qualify a column name with its table or view name, using the syntax table.column or view.column.

Subqueries Subqueries (i.e., forms of the SELECT command that appear inside other SQL statements), support this additional functionality:

• Subqueries can contain the GROUP BY clause.
• Subqueries can select from views whose defining queries contain the GROUP BY clause.

UPDATE

The UPDATE command supports this additional syntax:

• Database links to update data in tables and views on remote databases
• Table aliases for use with correlated subqueries
• Parenthesized lists of columns on the left side of the SET clause, rather than only single columns
• Subqueries on the right side of the SET clause, rather than only expressions
• PARTITION clause
• RETURNING clause

The UPDATE command also supports this additional functionality:

• An UPDATE statement may update a modifiable join view provided that all columns to be updated are in the same key-preserved table of the join. If the view specifies WITH CHECK OPTION, then join columns cannot be modified.
• A subquery within the SET clause or WHERE clause of an UPDATE statement can refer to the table or view being updated.
• A view containing columns that are defined as complex expressions (i.e., not simply as a column of a table in the FROM clause, but also containing functions or operators) can be updated.
• The UPDATE command supports updating a subquery.

Functions

This section describes additional functions and additional functionality of standard functions.

Additional Functions

The only Entry SQL92 functions are AVG, COUNT, MAX, MIN, and SUM. Oracle supports many additional functions that are not part of Entry SQL92. See "SQL Functions".

Additional Functionality of Standard Functions

You can nest group functions in the select list of a SELECT statement, as in this example:

SELECT MIN(MAX(sal)) 
FROM emp 
GROUP BY deptno; 

The depth of nesting cannot be more than that shown in the example.

You can also use a group function in a SELECT statement that queries a view whose defining query contains group functions or a GROUP BY clause.

Operators

This section describes additional operators and additional functionality of standard operators.

Additional Operators

Oracle supports these operators that are not part of Entry SQL92:

• || character operator (character concatenation)
• !=, ^=, and ¬= comparison operators (inequality)
• MINUS set operator
• INTERSECT set operator
• (+) operator (outer join)
• PRIOR operator

Additional Functionality of Standard Operators

Oracle supports additional functionality for Entry SQL92 operators:

• The left member of an expression containing the IN operator can be a parenthesized list of expressions, rather than only a single expression.
• Any expression, rather than only a column, can be used with the comparison operators IS NULL and IS NOT NULL.
• The pattern used with the LIKE operator can be any expression of datatype CHAR or VARCHAR2, rather than only a text literal.

Pseudocolumns

Pseudocolumns are values that behave like columns of a table but are not actually stored in the table. Pseudocolumns are supported by Oracle, but are not part of Entry SQL92. For a list of pseudocolumns, see "Pseudocolumns".

Datatypes

Oracle supports these additional datatypes that are not part of Entry SQL92:

• DATE (Note: Oracle's DATE datatype is different from the DATE data type in Intermediate SQL92.)
• NUMBER
• VARCHAR2
• LONG
• RAW
• LONG RAW
• ROWID
• BLOB
• CLOB
• BFILE
• NCLOB

Additionally, Oracle supports the following user-defined types that are not part of Entry SQL92:

• object
• REF
• collection (VARRAY and nested table)

Oracle also supports automatic conversion of values from one datatype to another that is not part of Entry SQL92.

Names of Schema Objects

Oracle supports additional functionality for names of schema objects:

• Oracle supports names of maximum length 30 bytes, rather than 18 Oracle supports names that contain the special characters # and $ and repeated underscores (__).

Values

Oracle allows you to use either uppercase "E" or lowercase "e" for exponential notation of numeric values, rather than only "E".

FIPS Flagger

In your Oracle applications, you can use the extensions listed in the previous sections 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, Pro*C/C++ Precompiler Programmer's Guide, and SQL*Module for Ada Programmer's Guide.

---