Go to main content
1/555
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Changes in This Release for Oracle Database SQL Language Reference
Changes in Oracle Database 12
c
Release 1 (12.1.0.2)
Changes in Oracle Database 12
c
Release 1 (12.1.0.1)
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
Basic Elements of Oracle SQL
Data Types
Oracle Built-in Data Types
CHAR Data Type
NCHAR Data Type
VARCHAR2 Data Type
VARCHAR Data Type
NVARCHAR2 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
Extended Data Types
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
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
CLUSTERING Hint
CURSOR_SHARING_EXACT Hint
DISABLE_PARALLEL_DML Hint
DRIVING_SITE Hint
DYNAMIC_SAMPLING Hint
ENABLE_PARALLEL_DML Hint
FACT Hint
FIRST_ROWS Hint
FULL Hint
GATHER_OPTIMIZER_STATISTICS 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
INMEMORY Hint
INMEMORY_PRUNING Hint
LEADING Hint
MERGE Hint
MODEL_MIN_ANALYSIS Hint
MONITOR Hint
NATIVE_FULL_OUTER_JOIN Hint
NOAPPEND Hint
NOCACHE Hint
NO_CLUSTERING Hint
NO_EXPAND Hint
NO_FACT Hint
NO_GATHER_OPTIMIZER_STATISTICS Hint
NO_INDEX Hint
NO_INDEX_FFS Hint
NO_INDEX_SS Hint
NO_INMEMORY Hint
NO_INMEMORY_PRUNING 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_PQ_CONCURRENT_UNION Hint
NO_PQ_SKEW 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_CUBE Hint
NO_USE_HASH Hint
NO_USE_MERGE Hint
NO_USE_NL Hint
NO_XML_QUERY_REWRITE Hint
NO_XMLINDEX_REWRITE Hint
NO_ZONEMAP Hint
OPT_PARAM Hint
ORDERED Hint
PARALLEL Hint
PARALLEL_INDEX Hint
PQ_CONCURRENT_UNION Hint
PQ_DISTRIBUTE Hint
PQ_FILTER Hint
PQ_SKEW 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_CUBE 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
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
Expressions
About SQL Expressions
Simple Expressions
Compound Expressions
CASE Expressions
Column Expressions
CURSOR Expressions
Datetime Expressions
Function Expressions
Interval Expressions
JSON Object Access Expressions
Model Expressions
Object Access Expressions
Placeholder Expressions
Scalar Subquery Expressions
Type Constructor Expressions
Expression Lists
6
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
JSON Conditions
IS JSON Condition
JSON_EXISTS Condition
JSON_TEXTCONTAINS Condition
Compound Conditions
BETWEEN Condition
EXISTS Condition
IN Condition
IS OF
type
Condition
7
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
JSON 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
APPROX_COUNT_DISTINCT
ASCII
ASCIISTR
ASIN
ATAN
ATAN2
AVG
BFILENAME
BIN_TO_NUM
BITAND
CARDINALITY
CAST
CEIL
CHARTOROWID
CHR
CLUSTER_DETAILS
CLUSTER_DISTANCE
CLUSTER_ID
CLUSTER_PROBABILITY
CLUSTER_SET
COALESCE
COLLECT
COMPOSE
CON_DBID_TO_ID
CON_GUID_TO_ID
CON_NAME_TO_ID
CON_UID_TO_ID
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_MAT_PARTITION
DATAOBJ_TO_PARTITION
DBTIMEZONE
DECODE
DECOMPOSE
DELETEXML
DENSE_RANK
DEPTH
DEREF
DUMP
EMPTY_BLOB, EMPTY_CLOB
EXISTSNODE
EXP
EXTRACT (datetime)
EXTRACT (XML)
EXTRACTVALUE
FEATURE_DETAILS
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
JSON_QUERY
JSON_TABLE
JSON_VALUE
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
ORA_INVOKING_USER
ORA_INVOKING_USERID
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
STANDARD_HASH
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_OP_ZONE_ID
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
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: ADMINISTER KEY MANAGEMENT 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
ADMINISTER KEY MANAGEMENT
ALTER AUDIT POLICY (Unified Auditing)
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 MATERIALIZED ZONEMAP
ALTER OPERATOR
ALTER OUTLINE
ALTER PACKAGE
ALTER PLUGGABLE DATABASE
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 SYNONYM
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 (Traditional Auditing)
AUDIT (Unified Auditing)
CALL
COMMENT
COMMIT
14
SQL Statements: CREATE AUDIT POLICY to CREATE JAVA
CREATE AUDIT POLICY (Unified Auditing)
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 MATERIALIZED ZONEMAP
CREATE OPERATOR
CREATE OUTLINE
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PFILE
CREATE PLUGGABLE DATABASE
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 AUDIT POLICY (Unified Auditing)
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 MATERIALIZED ZONEMAP
DROP OPERATOR
DROP OUTLINE
DROP PACKAGE
DROP PLUGGABLE DATABASE
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
EXPLAIN WORK
FLASHBACK DATABASE
FLASHBACK TABLE
GRANT
INSERT
LOCK TABLE
MERGE
NOAUDIT (Traditional Auditing)
NOAUDIT (Unified Auditing)
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:2011
Oracle Support for Optional Features of SQL/Foundation:2011
Oracle Compliance with SQL/CLI:2008
Oracle Compliance with SQL/PSM:2011
Oracle Compliance with SQL/MED:2008
Oracle Compliance with SQL/OLB:2008
Oracle Compliance with SQL/JRT:2008
Oracle Compliance with SQL/XML:2011
Oracle Compliance with SQL/RPR:2012
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
Scripting on this page enhances content navigation, but does not change the content in any way.