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