Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-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

Send Us Your Comments

Preface

Audience
Organization
Related Documentation
Conventions
Documentation Accessibility

What's New in the SQL Reference?

Oracle Database 10g 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
Nonstandard SQL
Recursive SQL
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 Example
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
REFs
Varrays
Nested Tables
Oracle-Supplied Types
Any Types
SYS.ANYTYPE
SYS.ANYDATA
SYS.ANYDATASET
XML Types
XMLType
URI Datatypes
URIFactory Package
Spatial Types
SDO_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 String 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
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
Hints
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
CONNECT_BY_ISLEAF
LEVEL
Sequence Pseudocolumns
Where to Use Sequence Values
How to Use Sequence Values
Version Query Pseudocolumns
OBJECT_ID
OBJECT_VALUE
ORA_ROWSCN
ROWID
ROWNUM
XMLDATA

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
CURSOR Expressions
Datetime Expressions
Function Expressions
Interval Expressions
Object Access Expressions
Scalar Subquery Expressions
Model Expressions
Type Constructor Expressions
Variable Expressions
Expression Lists

6 Conditions

About SQL Conditions
Condition Precedence
Comparison Conditions
Simple Comparison Conditions
Group Comparison Conditions
Floating-Point Conditions
Logical Conditions
Range Conditions
Null Conditions
Compound Conditions
EQUALS_PATH
EXISTS
IN
IS A SET
IS ANY
IS EMPTY
IS OF type
IS PRESENT
LIKE
MEMBER
REGEXP_LIKE
SUBMULTISET
UNDER_PATH

7 Functions

SQL Functions
Single-Row Functions
Numeric Functions
Character Functions Returning Character Values
Character Functions Returning Number Values
Datetime Functions
Conversion Functions
Collection Functions
Miscellaneous Single-Row Functions
Aggregate Functions
Analytic Functions
Object Reference Functions
Model Functions
ABS
ACOS
ADD_MONTHS
ASCII
ASCIISTR
ASIN
ATAN
ATAN2
AVG
BFILENAME
BIN_TO_NUM
BITAND
CARDINALITY
CAST
CEIL
CHARTOROWID
CHR
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
DENSE_RANK
DEPTH
DEREF
DUMP
EMPTY_BLOB, EMPTY_CLOB
EXISTSNODE
EXP
EXTRACT (datetime)
EXTRACT (XML)
EXTRACTVALUE
FIRST
FIRST_VALUE
FLOOR
FROM_TZ
GREATEST
GROUP_ID
GROUPING
GROUPING_ID
HEXTORAW
INITCAP
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
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
XMLCOLATTVAL
XMLCONCAT
XMLELEMENT
XMLFOREST
XMLSEQUENCE
XMLTRANSFORM
ROUND and TRUNC Date Functions
User-Defined Functions
Prerequisites
Name Precedence
Naming Conventions

8 Common SQL DDL Clauses

allocate_extent_clause
constraint
deallocate_unused_clause
file_specification
logging_clause
parallel_clause
physical_attributes_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
Shared Server Parameters

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 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 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:2003
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

D Oracle Database Reserved Words

E Examples

Using Extensible Indexing
Using XML in SQL Statements

Index