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