1/522

Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in the SQL Language Reference?

• Oracle Database 11g Release 2 (11.2.0.4) New Features in the SQL Language Reference
• Oracle Database 11g Release 2 (11.2.0.2) New Features in the SQL Language Reference
• Oracle Database 11g Release 2 (11.2.0.1) New Features in the SQL Language Reference
• Oracle Database 11g Release 1 New Features in the SQL Language Reference

1 Introduction to Oracle SQL

• History of SQL
• SQL Standards
  • How SQL Works
  • Common Language for All Relational Databases
• Using Enterprise Manager
• Lexical Conventions
• Tools Support

2 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

3 Basic Elements of Oracle SQL

• Data Types
  • Oracle Built-in Data Types
    • CHAR Data Type
    • NCHAR Data Type
    • NVARCHAR2 Data Type
    • VARCHAR2 Data Type
    • VARCHAR Data Type
    • NUMBER Data Type
    • FLOAT Data Type
    • Floating-Point Numbers
    • Numeric Precedence
    • DATE Data Type
    • TIMESTAMP Data Type
    • TIMESTAMP WITH TIME ZONE Data Type
    • TIMESTAMP WITH LOCAL TIME ZONE Data Type
    • INTERVAL YEAR TO MONTH Data Type
    • INTERVAL DAY TO SECOND Data Type
    • Datetime/Interval Arithmetic
    • Support for Daylight Saving Times
    • Datetime and Interval Examples
    • RAW and LONG RAW Data Types
    • BFILE Data Type
    • BLOB Data Type
    • CLOB Data Type
    • NCLOB Data Type
  • Rowid Data Types
    • ROWID Data Type
    • UROWID Data Type
  • ANSI, DB2, and SQL/DS Data Types
  • User-Defined Types
    • Object Types
    • REF Data Types
    • Varrays
    • Nested Tables
  • Oracle-Supplied Types
  • Any Types
    • ANYTYPE
    • ANYDATA
    • ANYDATASET
  • XML Types
    • XMLType
    • URI Data Types
    • URIFactory Package
  • Spatial Types
    • SDO_GEOMETRY
    • SDO_TOPO_GEOMETRY
    • SDO_GEORASTER
  • Media Types
  • Expression Filter Type
    • Expression
• Data Type Comparison Rules
  • Numeric Values
  • Date Values
  • Character Values
  • Object Values
  • Varrays and Nested Tables
  • Data Type Precedence
  • Data Conversion
    • Implicit and Explicit Data Conversion
    • Implicit Data Conversion
    • Implicit Data Conversion Examples
    • Explicit Data Conversion
  • Security Considerations for 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 and Nonschema Objects
  • Hints
  • Alphabetical Listing of Hints
    • ALL_ROWS Hint
    • APPEND Hint
    • APPEND_VALUES Hint
    • CACHE Hint
    • CHANGE_DUPKEY_ERROR_INDEX Hint
    • CLUSTER Hint
    • CURSOR_SHARING_EXACT Hint
    • DRIVING_SITE Hint
    • DYNAMIC_SAMPLING Hint
    • FACT Hint
    • FIRST_ROWS Hint
    • FULL Hint
    • HASH Hint
    • IGNORE_ROW_ON_DUPKEY_INDEX 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
    • MONITOR Hint
    • NATIVE_FULL_OUTER_JOIN 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_MONITOR Hint
    • NO_NATIVE_FULL_OUTER_JOIN 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_QUERY_TRANSFORMATION Hint
    • NO_RESULT_CACHE Hint
    • NO_REWRITE Hint
    • NOREWRITE Hint
    • NO_STAR_TRANSFORMATION Hint
    • NO_STATEMENT_QUEUING Hint
    • NO_UNNEST Hint
    • NO_USE_HASH Hint
    • NO_USE_MERGE Hint
    • NO_USE_NL Hint
    • NO_XML_QUERY_REWRITE Hint
    • NO_XMLINDEX_REWRITE Hint
    • OPT_PARAM Hint
    • ORDERED Hint
    • PARALLEL Hint
    • PARALLEL_INDEX Hint
    • PQ_DISTRIBUTE Hint
    • PUSH_PRED Hint
    • PUSH_SUBQ Hint
    • PX_JOIN_FILTER Hint
    • QB_NAME Hint
    • RESULT_CACHE Hint
    • RETRY_ON_ROW_CHANGE Hint
    • REWRITE Hint
    • STAR_TRANSFORMATION Hint
    • STATEMENT_QUEUING 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
• Database Object Names and Qualifiers
  • Database 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
  • References to Objects in Other Schemas
  • References to Objects in Remote Databases
    • Creating Database Links
    • References to Database Links
  • References to Partitioned Tables and Indexes
  • References to Object Type Attributes and Methods

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

• About SQL Functions
• Single-Row Functions
  • Numeric Functions
  • Character Functions Returning Character Values
  • Character Functions Returning Number Values
  • Character Set Functions
  • Datetime Functions
  • General Comparison Functions
  • Conversion Functions
  • Large Object Functions
  • Collection Functions
  • Hierarchical Functions
  • 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
• OLAP Functions
• Data Cartridge Functions
• ABS
• ACOS
• ADD_MONTHS
• APPENDCHILDXML
• ASCII
• ASCIISTR
• 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
• CUBE_TABLE
• CUME_DIST
• CURRENT_DATE
• CURRENT_TIMESTAMP
• CV
• DATAOBJ_TO_PARTITION
• 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
• INSERTCHILDXMLAFTER
• INSERTCHILDXMLBEFORE
• INSERTXMLAFTER
• INSERTXMLBEFORE
• INSTR
• ITERATION_NUMBER
• LAG
• LAST
• LAST_DAY
• LAST_VALUE
• LEAD
• LEAST
• LENGTH
• LISTAGG
• 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
• NLS_UPPER
• NLSSORT
• NTH_VALUE
• NTILE
• NULLIF
• NUMTODSINTERVAL
• NUMTOYMINTERVAL
• NVL
• NVL2
• ORA_DST_AFFECTED
• ORA_DST_CONVERT
• ORA_DST_ERROR
• ORA_HASH
• PATH
• PERCENT_RANK
• PERCENTILE_CONT
• PERCENTILE_DISC
• POWER
• POWERMULTISET
• POWERMULTISET_BY_CARDINALITY
• PREDICTION
• PREDICTION_BOUNDS
• PREDICTION_COST
• PREDICTION_DETAILS
• PREDICTION_PROBABILITY
• PREDICTION_SET
• PRESENTNNV
• PRESENTV
• PREVIOUS
• RANK
• RATIO_TO_REPORT
• RAWTOHEX
• RAWTONHEX
• REF
• REFTOHEX
• REGEXP_COUNT
• REGEXP_INSTR
• REGEXP_REPLACE
• REGEXP_SUBSTR
• REGR_ (Linear Regression) Functions
• REMAINDER
• REPLACE
• ROUND (date)
• ROUND (number)
• 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_BLOB
• 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 (date)
• TRUNC (number)
• TZ_OFFSET
• UID
• UNISTR
• UPDATEXML
• UPPER
• USER
• USERENV
• VALUE
• VAR_POP
• VAR_SAMP
• VARIANCE
• VSIZE
• WIDTH_BUCKET
• XMLAGG
• XMLCAST
• XMLCDATA
• XMLCOLATTVAL
• XMLCOMMENT
• XMLCONCAT
• XMLDIFF
• XMLELEMENT
• XMLEXISTS
• XMLFOREST
• XMLISVALID
• XMLPARSE
• XMLPATCH
• XMLPI
• XMLQUERY
• XMLROOT
• XMLSEQUENCE
• XMLSERIALIZE
• XMLTABLE
• XMLTRANSFORM
• ROUND and TRUNC Date Functions
• About User-Defined Functions
  • Prerequisites
  • Name Precedence

6 Expressions

• About SQL Expressions
• Simple Expressions
• Compound Expressions
• CASE Expressions
• Column Expressions
• CURSOR Expressions
• Datetime Expressions
• Function Expressions
• Interval Expressions
• Model Expressions
• Object Access Expressions
• Placeholder Expressions
• Scalar Subquery Expressions
• Type Constructor 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
• Null Conditions
• XML Conditions
  • EQUALS_PATH Condition
  • UNDER_PATH Condition
• Compound Conditions
• BETWEEN Condition
• 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 DATABASE LINK
• ALTER DIMENSION
• ALTER DISKGROUP
• ALTER FLASHBACK ARCHIVE
• ALTER FUNCTION
• ALTER INDEX
• ALTER INDEXTYPE
• ALTER JAVA

11 SQL Statements: ALTER LIBRARY to ALTER SYSTEM

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

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 EDITION
• CREATE FLASHBACK ARCHIVE
• 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 EDITION
• DROP FLASHBACK ARCHIVE
• 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 CLUSTER
• TRUNCATE TABLE
• 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 Automatic and Manual Locking Mechanisms During SQL Operations

• Automatic Locks in DML Operations
• Automatic Locks in DDL Operations
  • Exclusive DDL Locks
  • Share DDL Locks
  • Breakable Parse Locks
• Manual Data Locking

C Oracle and Standard SQL

• ANSI Standards
• ISO Standards
• Oracle Compliance To Core SQL:2008
• Oracle Support for Optional Features of SQL/Foundation:2008
• Oracle Compliance with SQL/CLI:2008
• Oracle Compliance with SQL/PSM:2008
• Oracle Compliance with SQL/MED:2008
• Oracle Compliance with SQL/OLB:2008
• Oracle Compliance with SQL/JRT:2008
• Oracle Compliance with SQL/XML:2008
• Oracle Compliance with FIPS 127-2
• Oracle Extensions to Standard SQL
• Oracle Compliance with Older Standards
• Character Set Support

D Oracle Regular Expression Support

• Multilingual Regular Expression Syntax
• Regular Expression Operator Multilingual Enhancements
• Perl-influenced Extensions in Oracle Regular Expressions

E Oracle SQL Reserved Words and Keywords

• Oracle SQL Reserved Words
• Oracle SQL Keywords

F Extended Examples

• Using Extensible Indexing
• Using XML in SQL Statements

Index