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
Oracle7 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 Oracle7 Server, the Oracle Precompilers Version 1.5, and SQL*Module Version 1.0 provide conformance with the ANSI X3.135-1992/ISO 9075-1992 standard:
- Compliance at Entry Level
(including both SQL-DDL and SQL-DML)
- Full implementation of the Integrity Enhancement Feature
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." Oracle complies completely with FIPS PUB 127, providing SQL conformance as described above. In addition, this information is provided regarding Section 13 "Special Procurement Considerations" of FIPS PUB 127.
Section 16.2 Programming Language Interfaces
The Oracle Precompilers support the use of Embedded SQL. SQL*Module supports the use of Module Language. Support is provided for Ada, C, COBOL, FORTRAN, and Pascal.
Section 16.3 Style of Language Interface
Oracle with SQL*Module supports Module Language for Ada, C, COBOL, FORTRAN, and Pascal. Oracle with the Oracle Precompilers supports Ada, C, COBOL, FORTRAN, and Pascal. The languages supported may vary depending on your operating system.
Section 16.5 Interactive Direct SQL
Oracle7 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:
- SELECT command, with ORDER BY clause but not INTO clause
Most other SQL commands described in this Manual are also supported interactively.
Section 16.6 Sizing for Database Constructs
Table 4 - 15 lists requirements identified in FIPS PUB 127-1 and how they are met by Oracle7.
| Length of an identifier (in bytes)
| 18
| 30
|
| Length of CHARACTER datatype (in bytes)
| 240
| 255
|
| 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
| 254
|
| Values in an INSERT statement
| 100
| 254
|
| Set clauses in an UPDATE statement (Note 1)
| 20
| 254
|
| Length of a row (Note 2, 3)
| 2000
| 2(254)
+ 231
+253(2000)
|
| Columns in a UNIQUE constraint
| 6
| 16
|
| Length of a UNIQUE constraint (Note 2)
| 120
| (Note 4)
|
| Length of foreign key column list (Note 2)
| 120
| (Note 4)
|
| Columns in a GROUP BY clause
| 6
| 255 (Note 5)
|
| Sort specifications in ORDER BY clause
| 6
| 255 (Note 5)
|
| Columns in a referential integrity constraint
| 6
| 16
|
| Tables referenced in a SQL statement
| 10
| No limit
|
| Cursors simultaneously open
| 10
| (Note 6)
|
| Items in a SELECT list
| 100
| 255
|
Table 4 - 15. Sizing for Database Constructs
1 The number of set clauses in an UPDATE statement refers to the number items separated by commas following the SET keyword.
2 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.
3 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 253 VARCHAR2 values, each of length 2000 bytes.
4 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.
5 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.
6 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 multi-byte character sets.
Extensions to Standard SQL
This section lists the additional features supported by Oracle that extend beyond standard SQL "Database Language SQL with Integrity Enhancement". This section provides information on these parts of the SQL language:
For information on the extensions to standard embedded SQL "Database Language Embedded SQL" supported by the Oracle Precompilers, see Programmer's Guide to the Oracle Precompilers.
Commands
This section describes these additional commands and additional syntax and functionality of standard commands. Oracle supports these commands that are not part of standard SQL:
| 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 USER
ALTER VIEW
ANALYZE
AUDIT
COMMENT
CREATE CONTROLFILE
CREATE CLUSTER
CREATE DATABASE
CREATE DATABASE LINK
CREATE FUNCTION
CREATE INDEX
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 USER
CREATE VIEW
DROP CLUSTER
DROP DATABASE LINK
DROP FUNCTION
DROP INDEX
DROP PACKAGE
DROP PROCEDURE
DROP PROFILE
DROP ROLLBACK SEGMENT
DROP ROLE
DROP SEQUENCE
DROP SNAPSHOT
DROP SNAPSHOT LOG
DROP SYNONYM
DROP TABLE
DROP TABLESPACE
EXPLAIN PLAN
NOAUDIT
RENAME
REVOKE
SAVEPOINT
SET TRANSACTION
TRUNCATE
|
Additional Parts of Standard Commands
Oracle supports additional syntax for some commands that are part of standard SQL.
COMMIT
The COMMIT command supports these additional clauses:
Also, standard SQL requires a COMMIT statement to include the WORK keyword. Oracle allows your COMMIT statements to either include or omit this keyword. Note that this keyword adds no functionality to the command.
CREATE TABLE
The CREATE TABLE command supports these additional parameters and clauses:
CONSTRAINT Clause The CONSTRAINT clause of the CREATE TABLE command supports these additional options and identifiers:
CREATE VIEW
The CREATE VIEW command supports this additional syntax:
- 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. Standard SQL does not support column aliases in SELECT statements.
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
Also, standard SQL 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.
GRANT
The GRANT command (System Privileges and Roles) is an extension to standard SQL.
The GRANT command (Object Privileges) supports other privileges on other objects in addition to the DELETE, INSERT, REFERENCES, SELECT, and UPDATE privileges on tables and views supported by standard SQL. 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 a subquery in the INTO clause, similar to inserting into a view.
ROLLBACK
The ROLLBACK command supports these additional clauses:
Also, standard SQL requires a ROLLBACK statement to include the WORK keyword. Oracle allows your ROLLBACK statements to either include or omit this keyword. Note that this keyword adds no functionality to the command.
SELECT
The SELECT command supports these additional clauses and syntax:
- Database links for querying tables, views, and snapshots on remote databases
- Outer join operator (+) for performing outer joins
- Column aliases 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 select list expressions or positions of select list expressions.
- This clause can qualify a column name with its table or view name, using the syntax table.column or view.column.
Queries Queries, or forms of the SELECT command that appear inside other SQL statements, support this additional functionality:
- Queries can contain the GROUP BY clause.
- Queries 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 queries
- Parenthesized lists of columns on the left side of the SET clause, rather than only single columns
- Queries on the right side of the SET clause, rather than only expressions
The UPDATE command also supports this additional functionality:
- An UPDATE statement that updates a view can contain a query.
- A query within an UPDATE statement can refer to the table or view being updated.
- If the columns of a view are based on both columns of the base table and expressions containing columns of the base table, an UPDATE statement can update values based on columns, but not values based on expressions. Standard SQL prohibits all updates to such views.
Functions
This section describes additional functions and additional functionality of standard functions.
Additional Functions
The only standard SQL functions are AVG, COUNT, MAX, MIN, and SUM. Oracle supports many additional functions that are not part of standard SQL. See section "Functions" ![[*]](jump.gif)
.
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 standard SQL:
- || character operator (character concatenation)
- !=, ^=, and ¬= comparison operators (inequality)
- (+) operator (outer join)
Additional Functionality of Standard Operators
Oracle supports additional functionality for standard SQL 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 standard SQL. For a list of pseudocolumns, see the section "Pseudocolumns" .
Datatypes
Oracle supports these additional datatypes that are not part of standard SQL:
Oracle also supports automatic conversion of values from one datatype to another that is not part of standard SQL.
Names of Schema Objects
Oracle supports additional functionality for names of schema objects:
- Oracle supports names of maximum length 30 bytes, rather than 18 characters.
- Oracle allows you to enter names in either lowercase or uppercase, rather than only in lowercase. However, note that names are not case-sensitive unless they are in double quotes.
- Oracle supports names in double quotes. Quoted identifiers allow you to use:
- names that are reserved words
- names that are case-sensitive
- names that contain spaces
- 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 standard SQL. 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 standard SQL 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 Programmer's Guide to the Oracle Precompilers or SQL*Module User's Guide and Reference.
