Preface

This reference contains a complete description of the Structured Query Language (SQL) used to manage information in an Oracle database. Oracle SQL is a superset of the American National Standards Institute (ANSI) and the International Standards Organization (ISO) SQL92 standard at entry level conformance.

See Also:

PL/SQL User's Guide and Reference for information on PL/SQL, Oracle's procedural language extension to SQL

Pro*C/C++ Precompiler Programmer's Guide, SQL*Module for Ada Programmer's Guide, and the Pro*COBOL Precompiler Programmer's Guide for detailed descriptions of Oracle embedded SQL

Features and Functionality

Oracle8i SQL Reference contains information about the features and functionality of the Oracle8i and the Oracle8i Enterprise Edition products. Oracle8i and Oracle8i Enterprise Edition have the same basic features. However, several advanced features are available only with the Enterprise Edition, and some of these are optional.

See Also:
Getting to Know Oracle8i for information about the differences between Oracle8i and the Oracle8i Enterprise Edition and the available features and options. That book also describes all the features that are new in Oracle8i.

Audience

This reference is intended for all users of Oracle SQL.

What's New in Oracle8i?

Each release of Oracle8i contains new features, many of which are documented throughout this reference.

See Also:
Getting to Know Oracle8i for a description of all features new to this release

Release 3 (8.1.7)

The following SQL functions are new to this release:

BITAND
NVL2

Release 2 (8.1.6)

The following SQL functions are new to this release:

In addition, the following features have been enhanced:

The aggregate functions have expanded functionality. See "Aggregate Functions".
When specifying LOB storage parameters, you can now specify caching of LOBs for read-only purposes. See CREATE TABLE .
The section on Expressions now contains a new expression. See "CASE Expressions".
Subqueries can now be unnested. See "Unnesting of Nested Subqueries".

Release 8.1.5

The following top-level SQL statements are new to Release 8.1.5:

Organization

This reference is divided into the following parts:

Chapter 1, "Introduction"

This chapter defines SQL and describes its history as well as the advantages of using it to access relational databases.

Chapter 2, "Basic Elements of Oracle SQL"

This chapter describes the basic building blocks of an Oracle database and of Oracle SQL.

Chapter 3, "Operators"

This chapter describes how to use SQL operators to combine data into expressions and conditions.

Chapter 4, "Functions"

This chapter describes how to use SQL functions to combine data into expressions and conditions.

Chapter 5, "Expressions, Conditions, and Queries"

This chapter describes SQL expressions and conditions and discusses the various ways of extracting information from your database through queries.

Chapter 6, "About SQL Statements"

This chapter lists the various types of SQL statements, and provides a table to help you find the appropriate SQL statement for your database task.

Chapter 7, "SQL Statements: ALTER CLUSTER to ALTER SYSTEM"

Chapter 8, "SQL Statements: ALTER TABLE to constraint_clause"

Chapter 9, "SQL Statements: CREATE CLUSTER to CREATE SEQUENCE"

Chapter 10, "SQL Statements: CREATE SYNONYM to DROP ROLLBACK SEGMENT"

Chapter 11, "SQL Statements: DROP SEQUENCE to UPDATE"

These chapters list and describe all Oracle SQL statements in alphabetical order.

Appendix A, "Syntax Diagrams"

This appendix describes how to read the syntax diagrams in this reference.

Appendix B, "Oracle and Standard SQL"

This appendix describes Oracle compliance with ANSI and ISO standards.

Appendix C, "Oracle Reserved Words"

This appendix lists words that are reserved for internal use by Oracle.

Structural Changes in the Reference in Release 8.1.7

The following top-level SQL statements have been revised in Release 8.1.7:

The two SQL statements GRANT object_privileges and GRANT system_privileges_and_roles have been combined into one GRANT statement. See GRANT .
The two SQL statements REVOKE schema_object_privileges and REVOKE system_privileges_and_roles have been combined into one REVOKE statement. See REVOKE .
The two SQL statements AUDIT sql_statements and AUDIT schema_objects have been combined into one AUDIT statement. See AUDIT .
The two SQL statements NOAUDIT sql_statements and NOAUDIT schema_objects have been combined into one NOAUDIT statement. See NOAUDIT .

Structural Changes in the Reference in Release 8.1.5

Users familiar with the Release 8.0 documentation will find that the following sections have been moved or renamed:

The section "Format Models" now appears in Chapter 2.
Chapter 3 has been divided into several smaller chapters:
- Chapter 3, "Operators"
- Chapter 4, "Functions"
- Chapter 5, "Expressions, Conditions, and Queries". The last section, "Queries and Subqueries", provides background for the syntactic and semantic information in SELECT and subquery .
A new chapter, Chapter 6, "About SQL Statements", has been added to help you find the correct SQL statement for a particular task.
The archive_log_clause is no longer a separate section, but has been incorporated into ALTER SYSTEM .
The deallocate_unused_clause is no longer a separate section, but has been incorporated into ALTER TABLE , ALTER CLUSTER , and ALTER INDEX .
The disable_clause is no longer a separate section, but has been incorporated into CREATE TABLE and ALTER TABLE .
The drop_clause is no longer a separate section. It has become the drop_constraint_clause of the ALTER TABLE statement (to distinguish it from the new drop_column_clause of that statement). See ALTER TABLE .
The enable_clause is no longer a separate section, but has been incorporated into CREATE TABLE and ALTER TABLE .
The parallel_clause is no longer a separate section. The clause has been simplified, and has been incorporated into the various statements where it is relevant.
The recover_clause is no longer a separate section. Recovery functionality has been enhanced, and because it is always implemented through the ALTER DATABASE statement, it has been incorporated into that section. See ALTER DATABASE .
The sections on snapshots and snapshot logs have been moved and renamed. Snapshot functionality has been greatly enhanced, and these objects are now called materialized views. See CREATE MATERIALIZED VIEW , ALTER MATERIALIZED VIEW , DROP MATERIALIZED VIEW , "CREATE MATERIALIZED VIEW LOG", ALTER MATERIALIZED VIEW LOG , and DROP MATERIALIZED VIEW LOG .
The section on subqueries has now been combined with the SELECT statement. See SELECT and subquery .

Conventions Used in this Reference

This section explains the conventions used in this book including:

Text

The text in this reference adheres to the following conventions:

UPPERCASE

Uppercase text calls attention to SQL keywords, filenames, and initialization parameters.

italics

Italicized text calls attention to parameters of SQL statements.

boldface

Boldface text calls attention to definitions of terms.

Syntax Diagrams and Notation

Syntax Diagrams

This reference uses syntax diagrams to show SQL statements in Chapter 7 through Chapter 11, and to show other elements of the SQL language in Chapter 2, "Basic Elements of Oracle SQL"; Chapter 3, "Operators"; Chapter 4, "Functions"; and Chapter 5, "Expressions, Conditions, and Queries". These syntax diagrams use lines and arrows to show syntactic structure, as shown here:

If you are not familiar with this type of syntax diagram, refer to Appendix A, "Syntax Diagrams", for a description of how to read them. This section describes the components of syntax diagrams and gives examples of how to write SQL statements. Syntax diagrams are made up of these items:

Keywords

Keywords have special meanings in the SQL language. In the syntax diagrams, keywords appear in UPPERCASE. You must use keywords in your SQL statements exactly as they appear in the syntax diagram, except that they can be either uppercase or lowercase. For example, you must use the CREATE keyword to begin your CREATE TABLE statements just as it appears in the CREATE TABLE syntax diagram.

Parameters

Parameters act as placeholders in syntax diagrams. They appear in lowercase. Parameters are usually names of database objects, Oracle datatype names, or expressions. When you see a parameter in a syntax diagram, substitute an object or expression of the appropriate type in your SQL statement. For example, to write a CREATE TABLE statement, use the name of the table you want to create, such as emp, in place of the table parameter in the syntax diagram. (Note that parameter names appear in italics in the text.)

Code Examples

This reference contains many examples of SQL statements. These examples show you how to use elements of SQL. The following example shows a CREATE TABLE statement:

CREATE TABLE accounts 
    ( accno     NUMBER, 
      owner     VARCHAR2(10), 
      balance   NUMBER(7,2) );

Code examples appear in a different font than the text.

Examples follow these conventions:

Keywords, such as CREATE and NUMBER, appear in uppercase.
Names of database objects and their parts, such as accounts and accno, appear in lowercase.
PL/SQL blocks appear in italics. Keywords and parameters in these blocks may not be documented in this reference unless they are also SQL keywords and parameters. For more information see PL/SQL User's Guide and Reference.

Many examples assume the existence of objects that are not created in the example itself. The examples will not work as expected unless you first create those underlying objects.

SQL is not case sensitive (except for quoted identifiers), so you need not follow these conventions when writing your own SQL statements. However, your statements may be easier for you to read if you do.

Some Oracle tools require you to terminate SQL statements with a special character. For example, the code examples in this reference were issued through SQL*Plus, and therefore are terminated with a semicolon (;). If you issue these example statements to Oracle, you must terminate them with the special character expected by the Oracle tool you are using.

Example Data

Many examples in this reference operate on sample tables. The definitions of some of these tables appear in a SQL script available on your distribution medium. On most operating systems the name of this script is UTLSAMPL.SQL, although its exact name and location depend on your operating system. This script creates sample users and creates these sample tables in the schema of the user scott (password tiger):

CREATE TABLE dept 
    (deptno    NUMBER(2)      CONSTRAINT pk_dept PRIMARY KEY, 
     dname     VARCHAR2(14), 
     loc       VARCHAR2(13) ); 
CREATE TABLE emp 
    (empno     NUMBER(4)      CONSTRAINT pk_emp PRIMARY KEY, 
     ename     VARCHAR2(10), 
     job       VARCHAR2(9), 
     mgr       NUMBER(4), 
     hiredate  DATE, 
     sal       NUMBER(7,2), 
     comm      NUMBER(7,2), 
     deptno    NUMBER(2)    CONSTRAINT fk_deptno REFERENCES dept ); 
CREATE TABLE bonus 
    (ename     VARCHAR2(10), 
     job       VARCHAR2(9), 
     sal       NUMBER, 
     comm      NUMBER ); 
CREATE TABLE salgrade 
    (grade     NUMBER, 
     losal     NUMBER, 
     hisal     NUMBER );

The script also fills the sample tables with this data:

SELECT * FROM dept;
 
DEPTNO  DNAME      LOC 
------- ---------- --------- 
10      ACCOUNTING NEW YORK 
20      RESEARCH   DALLAS 
30      SALES      CHICAGO 
40      OPERATIONS BOSTON 
 
SELECT * FROM emp; 

EMPNO  ENAME   JOB          MGR  HIREDATE    SAL   COMM  DEPTNO 
-----  ------- --------- ------ --------- ------ ------ ------- 
 7369  SMITH   CLERK       7902 17-DEC-80    800             20 
 7499  ALLEN   SALESMAN    7698 20-FEB-81   1600    300      30 
 7521  WARD    SALESMAN    7698 22-FEB-81   1250    500      30 
 7566  JONES   MANAGER     7839 02-APR-81   2975             20 
 7654  MARTIN  SALESMAN    7698 28-SEP-81   1250   1400      30 
 7698  BLAKE   MANAGER     7839 01-MAY-81   2850             30 
 7782  CLARK   MANAGER     7839 09-JUN-81   2450             10 
 7788  SCOTT   ANALYST     7566 19-APR-87   3000             20 
 7839  KING    PRESIDENT        17-NOV-81   5000             10 
 7844  TURNER  SALESMAN    7698 08-SEP-81   1500             30 
 7876  ADAMS   CLERK       7788 23-MAY-87   1100             20 
 7900  JAMES   CLERK       7698 03-DEC-81    950             30 
 7902  FORD    ANALYST     7566 03-DEC-81   3000             20 
 7934  MILLER  CLERK       7782 23-JAN-82   1300             10 
 
SELECT * FROM salgrade; 

GRADE LOSAL HISAL 
----- ----- ----- 
1     700   1200 
2     1201  1400 
3     1401  2000 
4     2001  3000 
5     3001  9999

The bonus table does not contain any data.

To perform all the operations of the script, run it when you are logged into Oracle as the user SYSTEM.

UPPERCASE	Uppercase text calls attention to SQL keywords, filenames, and initialization parameters.
italics	Italicized text calls attention to parameters of SQL statements.
boldface	Boldface text calls attention to definitions of terms.