Contents

Title and Copyright Information

Preface

Intended Audience

Documentation Accessibility

Related Documents

Conventions

What's New in the SQL Reference?

Oracle Database 10g Release 2 New Features in the SQL Reference

Oracle Database 10g Release 1 New Features in the SQL Reference

1 Introduction to Oracle SQL

History of SQL

SQL Standards

How SQL Works

Common Language for All Relational Databases

Recent Enhancements

Lexical Conventions

Tools Support

2 Basic Elements of Oracle SQL

Datatypes

Oracle Built-in Datatypes

CHAR Datatype

NCHAR Datatype

NVARCHAR2 Datatype

VARCHAR2 Datatype

VARCHAR Datatype

NUMBER Datatype

Floating-Point Numbers

Numeric Precedence

DATE Datatype

TIMESTAMP Datatype

TIMESTAMP WITH TIME ZONE Datatype

TIMESTAMP WITH LOCAL TIME ZONE Datatype

INTERVAL YEAR TO MONTH Datatype

INTERVAL DAY TO SECOND Datatype

Datetime/Interval Arithmetic

Support for Daylight Saving Times

Datetime and Interval Examples

RAW and LONG RAW Datatypes

BFILE Datatype

BLOB Datatype

CLOB Datatype

NCLOB Datatype

Restricted Rowids

Extended Rowids

Compatibility and Migration

UROWID Datatype

ANSI, DB2, and SQL/DS Datatypes

User-Defined Types

Object Types

REF Datatypes

Varrays

Nested Tables

Oracle-Supplied Types

Any Types

ANYTYPE

ANYDATA

ANYDATASET

XML Types

XMLType

URI Datatypes

URIFactory Package

Spatial Types

SDO_GEOMETRY

SDO_TOPO_GEOMETRY

SDO_GEORASTER

Media Types

ORDAudio

ORDImage

ORDImageSignature

ORDVideo

ORDDoc

SI_StillImage

SI_Color

SI_AverageColor

SI_ColorHistogram

SI_PositionalColor

SI_Texture

SI_FeatureList

Expression Filter Type

Expression

Datatype Comparison Rules

Numeric Values

Date Values

Character Values

Single Characters

Object Values

Varrays and Nested Tables

Data Conversion

Implicit and Explicit Data Conversion

Implicit Data Conversion

Implicit Data Conversion Examples

Explicit Data Conversion

Literals

Text Literals

Numeric Literals

Integer Literals

NUMBER and Floating-Point Literals

Datetime Literals

Interval Literals

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

Format Models

Number Format Models

Number Format Elements

Datetime Format Models

Datetime Format Elements

Datetime Format Elements and Globalization Support

ISO Standard Date Format Elements

The RR Datetime Format Element

Datetime Format Element Suffixes

Format Model Modifiers

Format Model Examples

String-to-Date Conversion Rules

XML Format Model

Nulls

Nulls in SQL Functions

Nulls with Comparison Conditions

Nulls in Conditions

Comments

Comments Within SQL Statements

Comments on Schema Objects

Using Hints

Alphabetical Listing of Hints

ALL_ROWS Hint

APPEND Hint

CACHE Hint

CLUSTER Hint

CURSOR_SHARING_EXACT Hint

DRIVING_SITE Hint

DYNAMIC_SAMPLING Hint

FACT Hint

FIRST_ROWS Hint

FULL Hint

HASH Hint

INDEX Hint

INDEX_ASC Hint

INDEX_COMBINE Hint

INDEX_DESC Hint

INDEX_FFS Hint

INDEX_JOIN Hint

INDEX_SS Hint

INDEX_SS_ASC Hint

INDEX_SS_DESC Hint

LEADING Hint

MERGE Hint

MODEL_MIN_ANALYSIS Hint

NOAPPEND Hint

NOCACHE Hint

NO_EXPAND Hint

NO_FACT Hint

NO_INDEX Hint

NO_INDEX_FFS Hint

NO_INDEX_SS Hint

NO_MERGE Hint

NO_PARALLEL Hint

NOPARALLEL Hint

NO_PARALLEL_INDEX Hint

NOPARALLEL_INDEX Hint

NO_PUSH_PRED Hint

NO_PUSH_SUBQ Hint

NO_PX_JOIN_FILTER Hint

NO_REWRITE Hint

NOREWRITE Hint

NO_QUERY_TRANSFORMATION Hint

NO_STAR_TRANSFORMATION Hint

NO_UNNEST Hint

NO_USE_HASH Hint

NO_USE_MERGE Hint

NO_USE_NL Hint

NO_XML_QUERY_REWRITE Hint

ORDERED Hint

PARALLEL Hint

PARALLEL_INDEX Hint

PQ_DISTRIBUTE Hint

PUSH_PRED Hint

PUSH_SUBQ Hint

PX_JOIN_FILTER Hint

QB_NAME Hint

REWRITE Hint

RULE Hint

STAR_TRANSFORMATION Hint

UNNEST Hint

USE_CONCAT Hint

USE_HASH Hint

USE_MERGE Hint

USE_NL Hint

USE_NL_WITH_INDEX Hint

Database Objects

Schema Objects

Nonschema Objects

Schema Object Names and Qualifiers

Schema Object Naming Rules

Schema Object Naming Examples

Schema Object Naming Guidelines

Syntax for Schema Objects and Parts in SQL Statements

How Oracle Database Resolves Schema Object References

Referring to Objects in Other Schemas

Referring to Objects in Remote Databases

Creating Database Links

Referring to Database Links

Referring to Partitioned Tables and Indexes

Referring to Object Type Attributes and Methods

3 Pseudocolumns

Hierarchical Query Pseudocolumns

CONNECT_BY_ISCYCLE Pseudocolumn

CONNECT_BY_ISLEAF Pseudocolumn

LEVEL Pseudocolumn

Sequence Pseudocolumns

Where to Use Sequence Values

How to Use Sequence Values

Version Query Pseudocolumns

COLUMN_VALUE Pseudocolumn

OBJECT_ID Pseudocolumn

OBJECT_VALUE Pseudocolumn

ORA_ROWSCN Pseudocolumn

ROWID Pseudocolumn

ROWNUM Pseudocolumn

XMLDATA Pseudocolumn

4 Operators

About SQL Operators

Unary and Binary Operators

Operator Precedence

Arithmetic Operators

Concatenation Operator

Hierarchical Query Operators

PRIOR

CONNECT_BY_ROOT

Set Operators

Multiset Operators

MULTISET EXCEPT

MULTISET INTERSECT

MULTISET UNION

User-Defined Operators

5 Functions

SQL Functions

Single-Row Functions

Numeric Functions

Character Functions Returning Character Values

NLS Character Functions

Character Functions Returning Number Values

Datetime Functions

General Comparison Functions

Conversion Functions

Large Object Functions

Collection Functions

Hierarchical Function

Data Mining Functions

XML Functions

Encoding and Decoding Functions

NULL-Related Functions

Environment and Identifier Functions

Aggregate Functions

Analytic Functions

Object Reference Functions

Model Functions

ABS

ACOS

ADD_MONTHS

APPENDCHILDXML

ASCIISTR

ASCII

ASIN

ATAN

ATAN2

AVG

BFILENAME

BIN_TO_NUM

BITAND

CARDINALITY

CAST

CEIL

CHARTOROWID

CHR

CLUSTER_ID

CLUSTER_PROBABILITY

CLUSTER_SET

COALESCE

COLLECT

COMPOSE

CONCAT

CONVERT

CORR

CORR_*

CORR_S

CORR_K

COS

COSH

COUNT

COVAR_POP

COVAR_SAMP

CUME_DIST

CURRENT_DATE

CURRENT_TIMESTAMP

CV

DBTIMEZONE

DECODE

DECOMPOSE

DELETEXML

DENSE_RANK

DEPTH

DEREF

DUMP

EMPTY_BLOB, EMPTY_CLOB

EXISTSNODE

EXP

EXTRACT (datetime)

EXTRACT (XML)

EXTRACTVALUE

FEATURE_ID

FEATURE_SET

FEATURE_VALUE

FIRST

FIRST_VALUE

FLOOR

FROM_TZ

GREATEST

GROUP_ID

GROUPING

GROUPING_ID

HEXTORAW

INITCAP

INSERTCHILDXML

INSERTXMLBEFORE

INSTR

ITERATION_NUMBER

LAG

LAST

LAST_DAY

LAST_VALUE

LEAD

LEAST

LENGTH

LN

LNNVL

LOCALTIMESTAMP

LOG

LOWER

LPAD

LTRIM

MAKE_REF

MAX

MEDIAN

MIN

MOD

MONTHS_BETWEEN

NANVL

NCHR

NEW_TIME

NEXT_DAY

NLS_CHARSET_DECL_LEN

NLS_CHARSET_ID

NLS_CHARSET_NAME

NLS_INITCAP

NLS_LOWER

NLSSORT

NLS_UPPER

NTILE

NULLIF

NUMTODSINTERVAL

NUMTOYMINTERVAL

NVL

NVL2

ORA_HASH

PATH

PERCENT_RANK

PERCENTILE_CONT

PERCENTILE_DISC

POWER

POWERMULTISET

POWERMULTISET_BY_CARDINALITY

PREDICTION

PREDICTION_COST

PREDICTION_DETAILS

PREDICTION_PROBABILITY

PREDICTION_SET

PRESENTNNV

PRESENTV

PREVIOUS

RANK

RATIO_TO_REPORT

RAWTOHEX

RAWTONHEX

REF

REFTOHEX

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_SUBSTR

REGR_ (Linear Regression) Functions

REMAINDER

REPLACE

ROUND (number)

ROUND (date)

ROW_NUMBER

ROWIDTOCHAR

ROWIDTONCHAR

RPAD

RTRIM

SCN_TO_TIMESTAMP

SESSIONTIMEZONE

SET

SIGN

SIN

SINH

SOUNDEX

SQRT

STATS_BINOMIAL_TEST

STATS_CROSSTAB

STATS_F_TEST

STATS_KS_TEST

STATS_MODE

STATS_MW_TEST

STATS_ONE_WAY_ANOVA

STATS_T_TEST_*

STATS_T_TEST_ONE

STATS_T_TEST_PAIRED

STATS_T_TEST_INDEP and STATS_T_TEST_INDEPU

STATS_WSR_TEST

STDDEV

STDDEV_POP

STDDEV_SAMP

SUBSTR

SUM

SYS_CONNECT_BY_PATH

SYS_CONTEXT

SYS_DBURIGEN

SYS_EXTRACT_UTC

SYS_GUID

SYS_TYPEID

SYS_XMLAGG

SYS_XMLGEN

SYSDATE

SYSTIMESTAMP

TAN

TANH

TIMESTAMP_TO_SCN

TO_BINARY_DOUBLE

TO_BINARY_FLOAT

TO_CHAR (character)

TO_CHAR (datetime)

TO_CHAR (number)

TO_CLOB

TO_DATE

TO_DSINTERVAL

TO_LOB

TO_MULTI_BYTE

TO_NCHAR (character)

TO_NCHAR (datetime)

TO_NCHAR (number)

TO_NCLOB

TO_NUMBER

TO_SINGLE_BYTE

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_YMINTERVAL

TRANSLATE

TRANSLATE ... USING

TREAT

TRIM

TRUNC (number)

TRUNC (date)

TZ_OFFSET

UID

UNISTR

UPDATEXML

UPPER

USER

USERENV

VALUE

VAR_POP

VAR_SAMP

VARIANCE

VSIZE

WIDTH_BUCKET

XMLAGG

XMLCDATA

XMLCOLATTVAL

XMLCOMMENT

XMLCONCAT

XMLELEMENT

XMLFOREST

XMLPARSE

XMLPI

XMLQUERY

XMLROOT

XMLSEQUENCE

XMLSERIALIZE

XMLTABLE

XMLTRANSFORM

ROUND and TRUNC Date Functions

User-Defined Functions

Prerequisites

Name Precedence

6 Expressions

About SQL Expressions

Simple Expressions

Compound Expressions

CASE Expressions

CURSOR Expressions

Datetime Expressions

Function Expressions

Interval Expressions

Object Access Expressions

Scalar Subquery Expressions

Model Expressions

Type Constructor Expressions

Variable Expressions

Expression Lists

7 Conditions

About SQL Conditions

Condition Precedence

Comparison Conditions

Simple Comparison Conditions

Group Comparison Conditions

Floating-Point Conditions

Logical Conditions

Model Conditions

IS ANY Condition

IS PRESENT Condition

Multiset Conditions

IS A SET Condition

IS EMPTY Condition

MEMBER Condition

SUBMULTISET Condition

Pattern-matching Conditions

LIKE Condition

REGEXP_LIKE Condition

Range Conditions

Null Conditions

XML Conditions

EQUALS_PATH Condition

UNDER_PATH Condition

Compound Conditions

EXISTS Condition

IN Condition

IS OF type Condition

8 Common SQL DDL Clauses

allocate_extent_clause

constraint

deallocate_unused_clause

file_specification

logging_clause

parallel_clause

physical_attributes_clause

size_clause

storage_clause

9 SQL Queries and Subqueries

About Queries and Subqueries

Creating Simple Queries

Hierarchical Queries

Hierarchical Query Examples

The UNION [ALL], INTERSECT, MINUS Operators

Sorting Query Results

Joins

Join Conditions

Equijoins

Self Joins

Cartesian Products

Inner Joins

Outer Joins

Antijoins

Semijoins

Using Subqueries

Unnesting of Nested Subqueries

Selecting from the DUAL Table

Distributed Queries

10 SQL Statements: ALTER CLUSTER to ALTER JAVA

Types of SQL Statements

Data Definition Language (DDL) Statements

Data Manipulation Language (DML) Statements

Transaction Control Statements

Session Control Statements

System Control Statement

Embedded SQL Statements

How the SQL Statement Chapters are Organized

ALTER CLUSTER

ALTER DATABASE

ALTER DIMENSION

ALTER DISKGROUP

ALTER FUNCTION

ALTER INDEX

ALTER INDEXTYPE

ALTER JAVA

11 SQL Statements: ALTER MATERIALIZED VIEW to ALTER SYSTEM

ALTER MATERIALIZED VIEW

ALTER MATERIALIZED VIEW LOG

ALTER OPERATOR

ALTER OUTLINE

ALTER PACKAGE

ALTER PROCEDURE

ALTER PROFILE

ALTER RESOURCE COST

ALTER ROLE

ALTER ROLLBACK SEGMENT

ALTER SEQUENCE

ALTER SESSION

Initialization Parameters and ALTER SESSION

Session Parameters and ALTER SESSION

ALTER SYSTEM

Initialization Parameters and ALTER SYSTEM

System Parameters and ALTER SYSTEM

12 SQL Statements: ALTER TABLE to ALTER TABLESPACE

ALTER TABLE

ALTER TABLESPACE

13 SQL Statements: ALTER TRIGGER to COMMIT

ALTER TRIGGER

ALTER TYPE

ALTER USER

ALTER VIEW

ANALYZE

ASSOCIATE STATISTICS

AUDIT

CALL

COMMENT

COMMIT

14 SQL Statements: CREATE CLUSTER to CREATE JAVA

CREATE CLUSTER

CREATE CONTEXT

CREATE CONTROLFILE

CREATE DATABASE

CREATE DATABASE LINK

CREATE DIMENSION

CREATE DIRECTORY

CREATE DISKGROUP

CREATE FUNCTION

CREATE INDEX

CREATE INDEXTYPE

CREATE JAVA

15 SQL Statements: CREATE LIBRARY to CREATE SPFILE

CREATE LIBRARY

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW LOG

CREATE OPERATOR

CREATE OUTLINE

CREATE PACKAGE

CREATE PACKAGE BODY

CREATE PFILE

CREATE PROCEDURE

CREATE PROFILE

CREATE RESTORE POINT

CREATE ROLE

CREATE ROLLBACK SEGMENT

CREATE SCHEMA

CREATE SEQUENCE

CREATE SPFILE

16 SQL Statements: CREATE SYNONYM to CREATE TRIGGER

CREATE SYNONYM

CREATE TABLE

CREATE TABLESPACE

CREATE TRIGGER

17 SQL Statements: CREATE TYPE to DROP ROLLBACK SEGMENT

CREATE TYPE

CREATE TYPE BODY

CREATE USER

CREATE VIEW

DELETE

DISASSOCIATE STATISTICS

DROP CLUSTER

DROP CONTEXT

DROP DATABASE

DROP DATABASE LINK

DROP DIMENSION

DROP DIRECTORY

DROP DISKGROUP

DROP FUNCTION

DROP INDEX

DROP INDEXTYPE

DROP JAVA

DROP LIBRARY

DROP MATERIALIZED VIEW

DROP MATERIALIZED VIEW LOG

DROP OPERATOR

DROP OUTLINE

DROP PACKAGE

DROP PROCEDURE

DROP PROFILE

DROP RESTORE POINT

DROP ROLE

DROP ROLLBACK SEGMENT

18 SQL Statements: DROP SEQUENCE to ROLLBACK

DROP SEQUENCE

DROP SYNONYM

DROP TABLE

DROP TABLESPACE

DROP TRIGGER

DROP TYPE

DROP TYPE BODY

DROP USER

DROP VIEW

EXPLAIN PLAN

FLASHBACK DATABASE

FLASHBACK TABLE

GRANT

INSERT

LOCK TABLE

MERGE

NOAUDIT

PURGE

RENAME

REVOKE

ROLLBACK

19 SQL Statements: SAVEPOINT to UPDATE

SAVEPOINT

SELECT

SET CONSTRAINT[S]

SET ROLE

SET TRANSACTION

TRUNCATE

UPDATE

A How to Read Syntax Diagrams

Graphic Syntax Diagrams

Required Keywords and Parameters

Optional Keywords and Parameters

Syntax Loops

Multipart Diagrams

Database Objects

Backus-Naur Form Syntax

B Oracle and Standard SQL

ANSI Standards

ISO Standards

Oracle Compliance To Core SQL:2003

Oracle Support for Optional Features of SQL/Foundation:2003

Oracle Compliance with SQL/CLI:2003

Oracle Compliance with SQL/PSM:2003

Oracle Compliance with SQL/MED:2003

Oracle Compliance with SQL/XML:2005

Oracle Compliance with FIPS 127-2

Oracle Extensions to Standard SQL

Character Set Support

C Oracle Regular Expression Support

Multilingual Regular Expression Syntax

Regular Expression Operator Multilingual Enhancements

Perl-influenced Extensions in Oracle Regular Expressions

D Oracle Database Reserved Words

E Examples

Using Extensible Indexing

Using XML in SQL Statements

Index