Oracle® Database
SQL Reference
10g Release 1 (10.1)
Part No. B10759-01
December 2003
Oracle Database SQL Reference 10g Release 1 (10.1)
Part No. B10759-01
Copyright © 1996, 2003 Oracle Corporation. All rights reserved.
Primary Authors: Diana Lorentz, Joan Gregoire
Contributors: Sundeep Abraham, Angela Amor, Rick Anderson, Vikas Arora, Hermann Baer, Cathy
Baird, Anand Baldalker, Cailein Barclay, Ruth Baylis, Eric Belden, Paula Bingham, Tolga Bozkaya, Mark
Callaghan, Thomas Chang, Dinesh Das, Souri Das, Jay Davison, Mark Dilman, Mike Feng, Ray Guzman,
John Haydu, Wei Hu, Ken Jacobs, Bob Jenkins, Vishy Karra, Thomas Keefe, Jonathan Klein, Vasudha
Krishnaswamy, Goutam Kulkarni, Poojan Kumar, Bill Lee, Geoff Lee, Yunrui Li, Likuo Lin, Peter Linsley,
Rich Long, Catherine Luu, Qianrong Ma, Vineet Marwah, Susan Mavris, Steve McGee, Michael Moeller,
Tony Morales, Ari Mozes, Gopal Mulagund, Sujatha Muthulingam, Muthu Olaggapan, Ananth
Raghavan, Jack Raitto, Anitha Ramarao, Siva Ravada, Viv Schupmann, Shrikanth Shankar, Vikram
Shukla, Bipul Sinha, Mike Stewart, Sankar Subramanian, Seema Sundara, Andreas Sundquist, Hal
Takahara, Ashish Thusoo, Rama Vissapragada, Steve Wertheimer, Andy Witkowski, Daniel Wong, Min
Xiao, Aravind Yalamanchi, Wanli Yang, Qin Yu, Tim Yu, Fred Zemke, Weiran Zhang
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent and other intellectual and industrial property
laws. Reverse engineering, disassembly or decompilation of the Programs, except to the extent required
to obtain interoperability with other independently created software or as specified by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error-free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on

1 Introduction to Oracle SQL
History of SQL .................................................................................................................................... 1-1
SQL Standards .................................................................................................................................... 1-2
How SQL Works .......................................................................................................................... 1-2
Common Language for All Relational Databases.................................................................... 1-3
Recent Enhancements ........................................................................................................................ 1-3
Nonstandard SQL ............................................................................................................................... 1-4
Recursive SQL ..................................................................................................................................... 1-5
Lexical Conventions ........................................................................................................................... 1-5
Tools Support ...................................................................................................................................... 1-5
iv
2 Basic Elements of Oracle SQL
Datatypes ............................................................................................................................................. 2-1
Oracle Built-in Datatypes ............................................................................................................ 2-7
CHAR Datatype .................................................................................................................. 2-10
NCHAR Datatype ............................................................................................................... 2-11
NVARCHAR2 Datatype .................................................................................................... 2-11
VARCHAR2 Datatype ....................................................................................................... 2-12
VARCHAR Datatype ......................................................................................................... 2-12
NUMBER Datatype ............................................................................................................ 2-12
Scale and Precision ...................................................................................................... 2-13
Negative Scale .............................................................................................................. 2-14
Scale Greater than Precision ...................................................................................... 2-14
Floating-Point Numbers .................................................................................................... 2-14
BINARY_FLOAT.......................................................................................................... 2-15
BINARY_DOUBLE....................................................................................................... 2-15
Numeric Precedence .......................................................................................................... 2-17
DATE Datatype ................................................................................................................... 2-20
Using Julian Days......................................................................................................... 2-23
TIMESTAMP Datatype ...................................................................................................... 2-23

URI Datatypes ..................................................................................................................... 2-45
URIFactory Package............................................................................................................ 2-46
Spatial Types .............................................................................................................................. 2-47
SDO_GEOMETRY............................................................................................................... 2-47
SDO_GEORASTER ............................................................................................................. 2-47
Media Types ............................................................................................................................... 2-48
ORDAudio ........................................................................................................................... 2-48
ORDImage ........................................................................................................................... 2-48
ORDImageSignature .......................................................................................................... 2-48
ORDVideo ........................................................................................................................... 2-48
ORDDoc ............................................................................................................................... 2-49
SI_StillImage ........................................................................................................................ 2-49
SI_Color ................................................................................................................................ 2-49
SI_AverageColor ................................................................................................................ 2-49
SI_ColorHistogram ............................................................................................................ 2-49
SI_PositionalColor .............................................................................................................. 2-49
SI_Texture ............................................................................................................................ 2-49
SI_FeatureList ..................................................................................................................... 2-49
Expression Filter Type ............................................................................................................... 2-50
Expression ............................................................................................................................ 2-50
Datatype Comparison Rules .......................................................................................................... 2-50
Numeric Values ......................................................................................................................... 2-50
vi
Date Values ................................................................................................................................. 2-51
Character String Values ............................................................................................................ 2-51
Single Characters ....................................................................................................................... 2-52
Object Values .............................................................................................................................. 2-54
Varrays and Nested Tables ....................................................................................................... 2-54
Data Conversion ........................................................................................................................ 2-54
Implicit and Explicit Data Conversion ............................................................................ 2-55

Nulls with Comparison Conditions ........................................................................................ 2-90
Nulls in Conditions ................................................................................................................... 2-90
Comments .......................................................................................................................................... 2-91
Comments Within SQL Statements ........................................................................................ 2-91
Comments on Schema Objects ................................................................................................. 2-93
Hints ............................................................................................................................................ 2-93
Database Objects ............................................................................................................................ 2-105
Schema Objects ........................................................................................................................ 2-106
Nonschema Objects ................................................................................................................. 2-106
Schema Object Names and Qualifiers ....................................................................................... 2-107
Schema Object Naming Rules ................................................................................................ 2-107
Schema Object Naming Examples ........................................................................................ 2-111
Schema Object Naming Guidelines ...................................................................................... 2-111
Syntax for Schema Objects and Parts in SQL Statements ...................................................... 2-112
How Oracle Database Resolves Schema Object References .............................................. 2-113
Referring to Objects in Other Schemas ................................................................................. 2-114
Referring to Objects in Remote Databases ........................................................................... 2-114
Creating Database Links ................................................................................................. 2-115
Database Link Names................................................................................................ 2-115
Username and Password .......................................................................................... 2-116
Database Connect String ........................................................................................... 2-116
Referring to Database Links ............................................................................................ 2-116
Referring to Partitioned Tables and Indexes ....................................................................... 2-117
Referring to Object Type Attributes and Methods .............................................................. 2-119
3 Pseudocolumns
Hierarchical Query Pseudocolumns .............................................................................................. 3-1
CONNECT_BY_ISCYCLE .......................................................................................................... 3-2
CONNECT_BY_ISLEAF ............................................................................................................. 3-2
LEVEL ........................................................................................................................................... 3-3
Sequence Pseudocolumns ................................................................................................................ 3-4

CURSOR Expressions ........................................................................................................................ 5-8
Datetime Expressions ...................................................................................................................... 5-10
Function Expressions ....................................................................................................................... 5-12
Interval Expressions ........................................................................................................................ 5-12
Object Access Expressions ............................................................................................................. 5-13
Scalar Subquery Expressions ......................................................................................................... 5-14
Model Expressions............................................................................................................................ 5-15
ix
Type Constructor Expressions ....................................................................................................... 5-16
Variable Expressions ....................................................................................................................... 5-18
Expression Lists ................................................................................................................................ 5-19
6 Conditions
About SQL Conditions ...................................................................................................................... 6-2
Condition Precedence .................................................................................................................. 6-4
Comparison Conditions ................................................................................................................... 6-4
Simple Comparison Conditions ................................................................................................ 6-6
Group Comparison Conditions ................................................................................................. 6-8
Floating-Point Conditions ................................................................................................................ 6-9
Logical Conditions............................................................................................................................ 6-10
Range Conditions ............................................................................................................................ 6-11
Null Conditions ............................................................................................................................... 6-12
Compound Conditions ................................................................................................................... 6-12
EQUALS_PATH ................................................................................................................................ 6-13
EXISTS ............................................................................................................................................... 6-14
IN ....................................................................................................................................................... 6-14
IS A SET ............................................................................................................................................. 6-17
IS ANY ............................................................................................................................................... 6-18
IS EMPTY .......................................................................................................................................... 6-19
IS OF type .......................................................................................................................................... 6-20
IS PRESENT ...................................................................................................................................... 6-21

BFILENAME ..................................................................................................................................... 7-26
BIN_TO_NUM .................................................................................................................................. 7-27
BITAND ............................................................................................................................................. 7-28
CARDINALITY ................................................................................................................................ 7-30
CAST .................................................................................................................................................. 7-30
CEIL .................................................................................................................................................... 7-34
CHARTOROWID ............................................................................................................................ 7-34
CHR .................................................................................................................................................... 7-35
COALESCE ........................................................................................................................................ 7-37
COLLECT .......................................................................................................................................... 7-39
COMPOSE ......................................................................................................................................... 7-39
CONCAT ............................................................................................................................................ 7-40
CONVERT ......................................................................................................................................... 7-41
CORR .................................................................................................................................................. 7-43
CORR_* .............................................................................................................................................. 7-45
CORR_S ....................................................................................................................................... 7-46
CORR_K ...................................................................................................................................... 7-47
xi
COS ..................................................................................................................................................... 7-47
COSH ................................................................................................................................................. 7-48
COUNT .............................................................................................................................................. 7-49
COVAR_POP .................................................................................................................................... 7-51
COVAR_SAMP ................................................................................................................................ 7-53
CUME_DIST ..................................................................................................................................... 7-54
CURRENT_DATE ............................................................................................................................ 7-56
CURRENT_TIMESTAMP .............................................................................................................. 7-57
CV ........................................................................................................................................................ 7-59
DBTIMEZONE ................................................................................................................................. 7-60
DECODE ............................................................................................................................................ 7-61
DECOMPOSE ................................................................................................................................... 7-63

LN ...................................................................................................................................................... 7-100
LNNVL ............................................................................................................................................. 7-101
LOCALTIMESTAMP ..................................................................................................................... 7-102
LOG .................................................................................................................................................. 7-103
LOWER ............................................................................................................................................ 7-104
LPAD ................................................................................................................................................. 7-105
LTRIM .............................................................................................................................................. 7-106
MAKE_REF ..................................................................................................................................... 7-107
MAX .................................................................................................................................................. 7-108
MEDIAN .......................................................................................................................................... 7-110
MIN ................................................................................................................................................... 7-112
MOD ................................................................................................................................................. 7-113
MONTHS_BETWEEN .................................................................................................................. 7-114
NANVL ............................................................................................................................................ 7-115
NCHR ............................................................................................................................................... 7-116
NEW_TIME ..................................................................................................................................... 7-117
NEXT_DAY ...................................................................................................................................... 7-118
NLS_CHARSET_DECL_LEN ...................................................................................................... 7-119
NLS_CHARSET_ID ....................................................................................................................... 7-120
NLS_CHARSET_NAME ............................................................................................................... 7-120
NLS_INITCAP ................................................................................................................................ 7-121
NLS_LOWER .................................................................................................................................. 7-123
NLSSORT ........................................................................................................................................ 7-123
NLS_UPPER .................................................................................................................................... 7-126
NTILE ............................................................................................................................................... 7-127
NULLIF ............................................................................................................................................ 7-128
NUMTODSINTERVAL ................................................................................................................. 7-129
NUMTOYMINTERVAL ................................................................................................................ 7-130
NVL ................................................................................................................................................... 7-131
xiii

RTRIM .............................................................................................................................................. 7-183
SCN_TO_TIMESTAMP ................................................................................................................ 7-184
SESSIONTIMEZONE ................................................................................................................... 7-185
SET .................................................................................................................................................... 7-186
SIGN ................................................................................................................................................. 7-187
xiv
SIN .................................................................................................................................................... 7-188
SINH ................................................................................................................................................. 7-188
SOUNDEX ....................................................................................................................................... 7-189
SQRT ................................................................................................................................................ 7-190
STATS_BINOMIAL_TEST ........................................................................................................... 7-191
STATS_CROSSTAB ....................................................................................................................... 7-193
STATS_F_TEST .............................................................................................................................. 7-194
STATS_KS_TEST ........................................................................................................................... 7-196
STATS_MODE ................................................................................................................................ 7-197
STATS_MW_TEST ......................................................................................................................... 7-198
STATS_ONE_WAY_ANOVA ....................................................................................................... 7-200
STATS_T_TEST_* .......................................................................................................................... 7-202
STATS_T_TEST_ONE ............................................................................................................. 7-203
STATS_T_TEST_PAIRED ....................................................................................................... 7-203
STATS_T_TEST_INDEP and STATS_T_TEST_INDEPU ................................................... 7-204
STATS_WSR_TEST ....................................................................................................................... 7-206
STDDEV .......................................................................................................................................... 7-207
STDDEV_POP ................................................................................................................................ 7-208
STDDEV_SAMP ............................................................................................................................ 7-210
SUBSTR ........................................................................................................................................... 7-212
SUM .................................................................................................................................................. 7-213
SYS_CONNECT_BY_PATH ......................................................................................................... 7-215
SYS_CONTEXT .............................................................................................................................. 7-216
SYS_DBURIGEN ........................................................................................................................... 7-221

TRANSLATE ................................................................................................................................... 7-253
TRANSLATE ... USING ................................................................................................................ 7-254
TREAT .............................................................................................................................................. 7-256
TRIM ................................................................................................................................................ 7-257
TRUNC (number) .......................................................................................................................... 7-258
TRUNC (date) ................................................................................................................................. 7-259
TZ_OFFSET ..................................................................................................................................... 7-260
UID ................................................................................................................................................... 7-261
UNISTR ........................................................................................................................................... 7-261
UPDATEXML ................................................................................................................................. 7-262
UPPER .............................................................................................................................................. 7-264
USER ................................................................................................................................................ 7-264
USERENV ........................................................................................................................................ 7-265
VALUE .............................................................................................................................................. 7-267
VAR_POP ........................................................................................................................................ 7-268
VAR_SAMP ..................................................................................................................................... 7-269
VARIANCE ..................................................................................................................................... 7-271
xvi
VSIZE ............................................................................................................................................... 7-272
WIDTH_BUCKET .......................................................................................................................... 7-273
XMLAGG ......................................................................................................................................... 7-275
XMLCOLATTVAL .......................................................................................................................... 7-277
XMLCONCAT ................................................................................................................................. 7-278
XMLELEMENT................................................................................................................................ 7-279
XMLFOREST .................................................................................................................................. 7-282
XMLSEQUENCE............................................................................................................................. 7-283
XMLTRANSFORM......................................................................................................................... 7-285
ROUND and TRUNC Date Functions........................................................................................ 7-286
User-Defined Functions ................................................................................................................ 7-287
Prerequisites .............................................................................................................................. 7-289

Selecting from the DUAL Table .................................................................................................... 9-19
Distributed Queries ......................................................................................................................... 9-20
10 SQL Statements: ALTER CLUSTER to ALTER JAVA
Types of SQL Statements ............................................................................................................... 10-1
Data Definition Language (DDL) Statements ....................................................................... 10-2
Data Manipulation Language (DML) Statements ................................................................. 10-3
Transaction Control Statements .............................................................................................. 10-3
Session Control Statements ...................................................................................................... 10-4
System Control Statement ........................................................................................................ 10-4
Embedded SQL Statements ...................................................................................................... 10-4
How the SQL Statement Chapters are Organized ..................................................................... 10-4
ALTER CLUSTER ............................................................................................................................ 10-6
ALTER DATABASE ....................................................................................................................... 10-11
ALTER DIMENSION .................................................................................................................... 10-62
ALTER DISKGROUP ................................................................................................................... 10-67
ALTER FUNCTION ....................................................................................................................... 10-84
ALTER INDEX ................................................................................................................................ 10-88
ALTER INDEXTYPE ................................................................................................................... 10-112
ALTER JAVA ................................................................................................................................. 10-115
11 SQL Statements: ALTER MATERIALIZED VIEW to ALTER SYSTEM
ALTER MATERIALIZED VIEW ................................................................................................... 11-2
ALTER MATERIALIZED VIEW LOG ....................................................................................... 11-21
ALTER OPERATOR ...................................................................................................................... 11-29
ALTER OUTLINE .......................................................................................................................... 11-33
ALTER PACKAGE ......................................................................................................................... 11-35
xviii
ALTER PROCEDURE ................................................................................................................... 11-40
ALTER PROFILE ............................................................................................................................ 11-44
ALTER RESOURCE COST .......................................................................................................... 11-48
ALTER ROLE .................................................................................................................................. 11-51

CREATE DIMENSION ................................................................................................................. 14-45
CREATE DIRECTORY .................................................................................................................. 14-52
CREATE DISKGROUP ................................................................................................................. 14-55
CREATE FUNCTION .................................................................................................................... 14-61
CREATE INDEX ............................................................................................................................. 14-75
CREATE INDEXTYPE ................................................................................................................. 14-105
CREATE JAVA .............................................................................................................................. 14-109
15 SQL Statements: CREATE LIBRARY to CREATE SPFILE
CREATE LIBRARY .......................................................................................................................... 15-2
CREATE MATERIALIZED VIEW ................................................................................................ 15-5
CREATE MATERIALIZED VIEW LOG .................................................................................... 15-33
CREATE OPERATOR ................................................................................................................... 15-42
CREATE OUTLINE ....................................................................................................................... 15-46
CREATE PACKAGE ...................................................................................................................... 15-50
CREATE PACKAGE BODY ......................................................................................................... 15-55
CREATE PFILE ............................................................................................................................... 15-60
CREATE PROCEDURE ................................................................................................................ 15-62
CREATE PROFILE ......................................................................................................................... 15-69
CREATE ROLE ............................................................................................................................... 15-77
CREATE ROLLBACK SEGMENT .............................................................................................. 15-81
CREATE SCHEMA ........................................................................................................................ 15-85
CREATE SEQUENCE .................................................................................................................... 15-88
CREATE SPFILE ............................................................................................................................. 15-93
16 SQL Statements: CREATE SYNONYM to CREATE TRIGGER
CREATE SYNONYM ...................................................................................................................... 16-2
CREATE TABLE ............................................................................................................................... 16-7
CREATE TABLESPACE ................................................................................................................ 16-80
CREATE TRIGGER ..................................................................................................................... 16-100
xx
17 SQL Statements: CREATE TYPE to DROP ROLLBACK SEGMENT

DROP TABLE .................................................................................................................................... 18-7
DROP TABLESPACE ..................................................................................................................... 18-11
DROP TRIGGER ............................................................................................................................ 18-15
xxi
DROP TYPE .................................................................................................................................... 18-16
DROP TYPE BODY ....................................................................................................................... 18-19
DROP USER ................................................................................................................................... 18-21
DROP VIEW ................................................................................................................................... 18-23
EXPLAIN PLAN ............................................................................................................................. 18-25
FLASHBACK DATABASE............................................................................................................ 18-30
FLASHBACK TABLE .................................................................................................................... 18-33
GRANT ............................................................................................................................................ 18-40
INSERT ............................................................................................................................................ 18-65
LOCK TABLE .................................................................................................................................. 18-85
MERGE ............................................................................................................................................ 18-89
NOAUDIT........................................................................................................................................ 18-94
PURGE ............................................................................................................................................. 18-99
RENAME ....................................................................................................................................... 18-102
REVOKE ........................................................................................................................................ 18-104
ROLLBACK .................................................................................................................................. 18-115
19 SQL Statements: SAVEPOINT to UPDATE
SAVEPOINT ..................................................................................................................................... 19-2
SELECT .............................................................................................................................................. 19-4
SET CONSTRAINT[S] ................................................................................................................. 19-61
SET ROLE ........................................................................................................................................ 19-63
SET TRANSACTION ................................................................................................................... 19-66
TRUNCATE ..................................................................................................................................... 19-70
UPDATE ........................................................................................................................................... 19-75
A How to Read Syntax Diagrams
Graphic Syntax Diagrams ................................................................................................................. A-1

Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this
publication. Your input is an important part of the information used for revision.
■
Did you find any errors?
■
Is the information clearly presented?
■
Do you need more information? If so, where?
■
Are the examples correct? Do you need more examples?
■
What features did you like most about this manual?
If you find any errors or have any other suggestions for improvement, please indicate the title and
part number of the documentation and the chapter, section, and page number (if available). You can
send comments to us in the following ways:
■
Electronic mail:
■
FAX: (650) 506-7227 Attn: Server Technologies Documentation Manager
■
Postal service:
Oracle Corporation
Oracle Server Technologies Documentation
500 Oracle Parkway, Mailstop 4op11
Redwood Shores, CA 94065
U.S.A.
If you would like a reply, please give your name, address, telephone number, and (optionally) your
electronic mail address.
If you have problems with the software, please contact your local Oracle Support Services.
xxiv