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
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Oracle7, Oracle8, Oracle8i, Oracle9i, Oracle Store, PL/SQL,
Pro*C/C++, Pro*COBOL, SQL*Plus, and iSQL*Plus are trademarks or registered trademarks of Oracle
Corporation. Other names may be trademarks of their respective owners.
iii
Contents
Send Us Your Comments .............................................................................................................. xxiii
Preface......................................................................................................................................................... xxv
Audience .............................................................................................................................................. xxv
Organization........................................................................................................................................ xxv
Related Documentation ................................................................................................................... xxvii
Conventions...................................................................................................................................... xxviii
Documentation Accessibility ........................................................................................................... xxxi
What's New in the SQL Reference? ....................................................................................... xxxiii
Oracle Database 10g New Features in the SQL Reference......................................................... xxxiii
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
TIMESTAMP WITH TIME ZONE Datatype .................................................................. 2-24
TIMESTAMP WITH LOCAL TIME ZONE Datatype ................................................... 2-25
INTERVAL YEAR TO MONTH Datatype ...................................................................... 2-26
INTERVAL DAY TO SECOND Datatype ....................................................................... 2-26
Datetime/Interval Arithmetic .......................................................................................... 2-27
Support for Daylight Saving Times ................................................................................. 2-29
Datetime and Interval Example......................................................................................... 2-30
RAW and LONG RAW Datatypes ................................................................................... 2-30
BFILE Datatype ................................................................................................................... 2-35
BLOB Datatype ................................................................................................................... 2-36
CLOB Datatype ................................................................................................................... 2-36
NCLOB Datatype ................................................................................................................ 2-36
Restricted Rowids ............................................................................................................... 2-37
Extended Rowids ................................................................................................................ 2-37
Compatibility and Migration ............................................................................................ 2-38
UROWID Datatype ............................................................................................................ 2-38
v
ANSI, DB2, and SQL/DS Datatypes ....................................................................................... 2-39
User-Defined Types ................................................................................................................... 2-41
Object Types ........................................................................................................................ 2-41
REFs ...................................................................................................................................... 2-42
Varrays ................................................................................................................................. 2-42
Nested Tables ...................................................................................................................... 2-42
Oracle-Supplied Types ............................................................................................................. 2-43
Any Types ................................................................................................................................... 2-43
SYS.ANYTYPE..................................................................................................................... 2-44
SYS.ANYDATA................................................................................................................... 2-44
SYS.ANYDATASET............................................................................................................ 2-44
XML Types .................................................................................................................................. 2-44
XMLType ............................................................................................................................. 2-44
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
