Skip Headers
Oracle® Database SQL Reference
10g Release 2 (10.2)

Part Number B14200-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to next page
Next
View PDF

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