Table of Contents
- Title and Copyright Information
- Preface
- Changes in This Release for Oracle Database SQL Language Reference
- 1 Introduction to Oracle SQL
-
2
Basic Elements of Oracle SQL
-
Data Types
- Oracle Built-in Data Types
- Rowid Data Types
- ANSI, DB2, and SQL/DS Data Types
- User-Defined Types
- Oracle-Supplied Types
- Any Types
- XML Types
- Spatial Types
- Data Type Comparison Rules
- Literals
- Format Models
- Nulls
-
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
- CONTAINERS Hint
- CURSOR_SHARING_EXACT Hint
- DISABLE_PARALLEL_DML Hint
- DRIVING_SITE Hint
- DYNAMIC_SAMPLING Hint
- ENABLE_PARALLEL_DML Hint
- FACT Hint
- FIRST_ROWS Hint
- FRESH_MV Hint
- FULL Hint
- GATHER_OPTIMIZER_STATISTICS Hint
- GROUPING 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_BAND 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
- OPTIMIZER_FEATURES_ENABLE 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_BAND Hint
- USE_CONCAT Hint
- USE_CUBE Hint
- USE_HASH Hint
- USE_MERGE Hint
- USE_NL Hint
- USE_NL_WITH_INDEX Hint
- Database Objects
- Database Object Names and Qualifiers
- Syntax for Schema Objects and Parts in SQL Statements
-
Data Types
- 3 Pseudocolumns
- 4 Operators
-
5
Expressions
- About SQL Expressions
- Simple Expressions
- Analytic View 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
-
7
Functions
- About SQL Functions
-
Single-Row Functions
- Numeric Functions
- Character Functions Returning Character Values
- Character Functions Returning Number Values
- Character Set Functions
- Collation 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
- ANY_VALUE
- APPROX_COUNT
- APPROX_COUNT_DISTINCT
- APPROX_COUNT_DISTINCT_AGG
- APPROX_COUNT_DISTINCT_DETAIL
- APPROX_MEDIAN
- APPROX_PERCENTILE
- APPROX_PERCENTILE_AGG
- APPROX_PERCENTILE_DETAIL
- APPROX_RANK
- APPROX_SUM
- ASCII
- ASCIISTR
- ASIN
- ATAN
- ATAN2
- AVG
- BFILENAME
- BIN_TO_NUM
- BITAND
- BITMAP_BIT_POSITION
- BITMAP_BUCKET_NUMBER
- BITMAP_CONSTRUCT_AGG
- BITMAP_COUNT
- BITMAP_OR_AGG
- CARDINALITY
- CAST
- CEIL
- CHARTOROWID
- CHR
- CLUSTER_DETAILS
- CLUSTER_DISTANCE
- CLUSTER_ID
- CLUSTER_PROBABILITY
- CLUSTER_SET
- COALESCE
- COLLATION
- COLLECT
- COMPOSE
- CON_DBID_TO_ID
- CON_GUID_TO_ID
- CON_ID_TO_CON_NAME
- CON_ID_TO_DBID
- CON_NAME_TO_ID
- CON_UID_TO_ID
- CONCAT
- CONVERT
- CORR
- CORR_*
- 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
- DENSE_RANK
- DEPTH
- DEREF
- DUMP
- EMPTY_BLOB, EMPTY_CLOB
- EXISTSNODE
- EXP
- EXTRACT (datetime)
- EXTRACT (XML)
- EXTRACTVALUE
- FEATURE_COMPARE
- FEATURE_DETAILS
- FEATURE_ID
- FEATURE_SET
- FEATURE_VALUE
- FIRST
- FIRST_VALUE
- FLOOR
- FROM_TZ
- GREATEST
- GROUP_ID
- GROUPING
- GROUPING_ID
- HEXTORAW
- INITCAP
- INSTR
- ITERATION_NUMBER
- JSON_ARRAY
- JSON_ARRAYAGG
- JSON_DATAGUIDE
- JSON_MERGEPATCH
- JSON_OBJECT
- JSON_OBJECTAGG
- JSON_QUERY
- JSON_SERIALIZE
- JSON_TABLE
- JSON_TRANSFORM
- 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_COLLATION_ID
- NLS_COLLATION_NAME
- NLS_INITCAP
- NLS_LOWER
- NLS_UPPER
- NLSSORT
- NTH_VALUE
- NTILE
- NULLIF
- NUMTODSINTERVAL
- NUMTOYMINTERVAL
- NVL
- NVL2
- ORA_DM_PARTITION_NAME
- 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)
- ROUND_TIES_TO_EVEN (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_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_APPROX_COUNT_DISTINCT
- TO_APPROX_PERCENTILE
- TO_BINARY_DOUBLE
- TO_BINARY_FLOAT
- TO_BLOB (bfile)
- TO_BLOB (raw)
- TO_CHAR (bfile|blob)
- TO_CHAR (character)
- TO_CHAR (datetime)
- TO_CHAR (number)
- TO_CLOB (bfile|blob)
- TO_CLOB (character)
- 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_UTC_TIMESTAMP_TZ
- TO_YMINTERVAL
- TRANSLATE
- TRANSLATE ... USING
- TREAT
- TRIM
- TRUNC (date)
- TRUNC (number)
- TZ_OFFSET
- UID
- UNISTR
- UPPER
- USER
- USERENV
- VALIDATE_CONVERSION
- 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
- 8 Common SQL DDL Clauses
- 9 SQL Queries and Subqueries
-
10
SQL Statements: ADMINISTER KEY MANAGEMENT to ALTER JAVA
- Types of SQL Statements
- How the SQL Statement Chapters are Organized
- ADMINISTER KEY MANAGEMENT
- ALTER ANALYTIC VIEW
- ALTER ATTRIBUTE DIMENSION
- ALTER AUDIT POLICY (Unified Auditing)
- ALTER CLUSTER
- ALTER DATABASE
- ALTER DATABASE DICTIONARY
- ALTER DATABASE LINK
- ALTER DIMENSION
- ALTER DISKGROUP
- ALTER FLASHBACK ARCHIVE
- ALTER FUNCTION
- ALTER HIERARCHY
- ALTER INDEX
- ALTER INDEXTYPE
- ALTER INMEMORY JOIN GROUP
- ALTER JAVA
- 11 SQL Statements: ALTER LIBRARY to ALTER SESSION
- 12 SQL Statements: ALTER SYNONYM to COMMENT
-
13
SQL Statements: COMMIT to CREATE JAVA
- COMMIT
- CREATE ANALYTIC VIEW
- CREATE ATTRIBUTE DIMENSION
- 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 HIERARCHY
- CREATE INDEX
- CREATE INDEXTYPE
- CREATE INMEMORY JOIN GROUP
- CREATE JAVA
-
14
SQL Statements: CREATE LIBRARY to CREATE SCHEMA
- CREATE LIBRARY
- CREATE LOCKDOWN PROFILE
- 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
- 15 SQL Statements: CREATE SEQUENCE to DROP CLUSTER
- 16 SQL Statements: DROP CONTEXT to DROP JAVA
- 17 SQL Statements: DROP LIBRARY to DROP SYNONYM
- 18 SQL Statements: DROP TABLE to LOCK TABLE
- 19 SQL Statements: MERGE to UPDATE
- A How to Read Syntax Diagrams
- B Automatic and Manual Locking Mechanisms During SQL Operations
-
C
Oracle and Standard SQL
- ANSI Standards
- ISO Standards
- Oracle Compliance to Core SQL
- Oracle Support for Optional Features of SQL/Foundation
- Oracle Compliance with SQL/CLI
- Oracle Compliance with SQL/PSM
- Oracle Compliance with SQL/MED
- Oracle Compliance with SQL/OLB
- Oracle Compliance with SQL/JRT
- Oracle Compliance with SQL/XML
- Oracle Compliance with FIPS 127-2
- Oracle Extensions to Standard SQL
- Oracle Compliance with Older Standards
- Character Set Support
- D Oracle Regular Expression Support
- E Oracle SQL Reserved Words and Keywords
- F Extended Examples
- Index