Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

Organization

Related Documentation

Conventions

Documentation Accessibility

What's New in the SQL Reference?

Oracle Database 10g 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

Nonstandard SQL

Recursive SQL

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 Example

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

REFs

Varrays

Nested Tables

Oracle-Supplied Types

Any Types

SYS.ANYTYPE

SYS.ANYDATA

SYS.ANYDATASET

XML Types

XMLType

URI Datatypes

URIFactory Package

Spatial Types

SDO_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 String 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

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

Hints

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

CONNECT_BY_ISLEAF

LEVEL

Sequence Pseudocolumns

Where to Use Sequence Values

How to Use Sequence Values

Version Query Pseudocolumns

OBJECT_ID

OBJECT_VALUE

ORA_ROWSCN

ROWID

ROWNUM

XMLDATA

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

6 Conditions

About SQL Conditions

Condition Precedence

Comparison Conditions

Simple Comparison Conditions

Group Comparison Conditions

Floating-Point Conditions

Logical Conditions

Range Conditions

Null Conditions

Compound Conditions

EQUALS_PATH

EXISTS

IN

IS A SET

IS ANY

IS EMPTY

IS OF type

IS PRESENT

LIKE

MEMBER

REGEXP_LIKE

SUBMULTISET

UNDER_PATH

7 Functions

SQL Functions

Single-Row Functions

Numeric Functions

Character Functions Returning Character Values

Character Functions Returning Number Values

Datetime Functions

Conversion Functions

Collection Functions

Miscellaneous Single-Row Functions

Aggregate Functions

Analytic Functions

Object Reference Functions

Model Functions

ABS

ACOS

ADD_MONTHS

ASCII

ASCIISTR

ASIN

ATAN

ATAN2

AVG

BFILENAME

BIN_TO_NUM

BITAND

CARDINALITY

CAST

CEIL

CHARTOROWID

CHR

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

DENSE_RANK

DEPTH

DEREF

DUMP

EMPTY_BLOB, EMPTY_CLOB

EXISTSNODE

EXP

EXTRACT (datetime)

EXTRACT (XML)

EXTRACTVALUE

FIRST

FIRST_VALUE

FLOOR

FROM_TZ

GREATEST

GROUP_ID

GROUPING

GROUPING_ID

HEXTORAW

INITCAP

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

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

XMLCOLATTVAL

XMLCONCAT

XMLELEMENT

XMLFOREST

XMLSEQUENCE

XMLTRANSFORM

ROUND and TRUNC Date Functions

User-Defined Functions

Prerequisites

Name Precedence

Naming Conventions

8 Common SQL DDL Clauses

allocate_extent_clause

constraint

deallocate_unused_clause

file_specification

logging_clause

parallel_clause

physical_attributes_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

Shared Server Parameters

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

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

D Oracle Database Reserved Words

E Examples

Using Extensible Indexing

Using XML in SQL Statements

Index