Oracle7 Server SQL Reference

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:

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

COMMENT clause

FORCE clause

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:

PCTFREE parameter

PCTUSED parameter

INITRANS parameter

MAXTRANS parameter

TABLESPACE parameter

STORAGE clause

CLUSTER clause

ENABLE clause

DISABLE clause

AS clause

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

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

TO clause

FORCE clause

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:

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

Column aliases in the select list

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 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"

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)

MINUS set operator

INTERSECT set operator

(+) operator (outer join)

PRIOR operator

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:

DATE

NUMBER

VARCHAR2

LONG

LONG RAW

ROWID

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