Oracle8i. SQL Reference. Release 8.1.5

880 Pages·1999·3.508 MB·English

by  Diana Lorentz

Oracle8i
SQL Reference
Release 8.1.5
February 1999
Part No. A67779-01
SQL Reference, Release 8.1.5
Part No. A67779-01
Copyright © 1996, 1999, Oracle Corporation. All rights reserved.
Primary Authors: Diana Lorentz, Denise Oertel
Contributors: Alan Downing, Alex Tsukerman, Alok Pareek, Amit Ganesh, Andre Kruglikov, Andrew
Witkowski, Angela Amor, AnhTuan Tran, Ann Rhee, Aravind Yalamanchi, Ari Mozes, Arvind
Nithrakashyap, Ashok Joshi, Ashwini Surpur, Bhaskar Himatsingka, Bill Courington, Bill Waddington,
Brajesh Goyal, Cetin Ozbutun, Chin-Heng Hong, Chon Lei, Daniel Wong, Dinesh Das, Edward Waugh, Eric
Magrath, Franco Putzolu, Guhan Viswanathan, Harry Sun, Harvey Eneman, Jack Raitto, Jags Srinivasan,
Janaki Krishnaswami, Jerry Schwarz, Jianping Yang, Jim Finnerty, John Haydu, Joyo Wijaya, Juan Tellez,
Karuna Muthiah, Lilian Hobbs, Lois Price, Maria Pratt, Mark Jungerman, Michael Depledge, Mohamed
Zait, Muralidhar Krishnaprasad, Namit Jain, Nipun Agarwal, Paul Justus, Paul Raveling, Qin Yu,
Radhakrishna Hari, Ravi Murthy, Rick Anderson, Rick Wessman, Robert Jenkins, Rosanne Park, Sanjay
Kaluskar, Sankar Subramanian, Sophia Yeung, Sriram Samu, Steve Vivian, Subramanian Muralidhar,
Sukhjit Singh, Susan Kotsovolos, Thong Bui, Thuvan Hoang, Vikas Arora, Vinay Srihari, Vishu
Krishnamurthy, Vishy Karra, Wei Huang, Wei Wang
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 disclaims liability for any damages caused by such
use of the Programs.
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 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.
Oracle is a registered trademark, and Pro*COBOL, SQL*Plus, Net8, Oracle Call Interface, Oracle7, Oracle7
Server, Oracle8, Oracle8 Server, Oracle8i, Oracle Forms, PL/SQL, Pro*C, Pro*C/C++, and Trusted Oracle
are registered trademarks or trademarks of Oracle Corporation. All other company or product names
mentioned are used for identification purposes only and may be trademarks of their respective owners.
Contents
Send Us Your Comments................................................................................................................... ix
Preface............................................................................................................................................................ xi
Features and Functionality................................................................................................................... xi
Audience................................................................................................................................................. xii
How this Reference Is Organized...................................................................................................... xii
What’s New in This Release?............................................................................................................ xiii
Conventions Used in this Reference................................................................................................. xv
Your Comments Are Welcome............................................................................................................ xx
1 Introduction
History of SQL..................................................................................................................................... 1-1
SQL Standards..................................................................................................................................... 1-2
Embedded SQL................................................................................................................................... 1-3
Lexical Conventions........................................................................................................................... 1-4
Tools Support....................................................................................................................................... 1-5
2 Basic Elements of Oracle SQL
Literals................................................................................................................................................... 2-2
Datatypes.............................................................................................................................................. 2-5
Format Models................................................................................................................................... 2-33
Nulls.................................................................................................................................................... 2-49
Pseudocolumns................................................................................................................................. 2-51
Comments........................................................................................................................................... 2-56
iii
Database Objects............................................................................................................................... 2-63
Schema Object Names and Qualifiers.......................................................................................... 2-67
Referring to Schema Objects and Parts........................................................................................ 2-71
3 Operators
Unary and Binary Operators............................................................................................................. 3-1
Precedence............................................................................................................................................ 3-2
Arithmetic Operators.......................................................................................................................... 3-3
Concatenation Operator..................................................................................................................... 3-3
Comparison Operators....................................................................................................................... 3-5
Logical Operators.............................................................................................................................. 3-10
Set Operators...................................................................................................................................... 3-12
Other Built-In Operators................................................................................................................. 3-15
User-Defined Operators................................................................................................................... 3-16
4 Functions
SQL Functions..................................................................................................................................... 4-1
User-Defined Functions................................................................................................................... 4-56
5 Expressions, Conditions, and Queries
Expressions........................................................................................................................................... 5-1
Conditions.......................................................................................................................................... 5-13
Queries and Subqueries.................................................................................................................. 5-18
6 About SQL Statements
Summary of SQL Statements............................................................................................................ 6-1
Finding the Right SQL Statement................................................................................................... 6-5
7 SQL Statements
ALTER CLUSTER............................................................................................................................... 7-2
ALTER DATABASE............................................................................................................................ 7-6
ALTER DIMENSION....................................................................................................................... 7-24
ALTER FUNCTION.......................................................................................................................... 7-27
iv
ALTER INDEX................................................................................................................................... 7-29
ALTER JAVA...................................................................................................................................... 7-43
ALTER MATERIALIZED VIEW / SNAPSHOT.......................................................................... 7-45
ALTER MATERIALIZED VIEW LOG / SNAPSHOT LOG...................................................... 7-54
ALTER OUTLINE............................................................................................................................. 7-58
ALTER PACKAGE............................................................................................................................ 7-59
ALTER PROCEDURE...................................................................................................................... 7-62
ALTER PROFILE............................................................................................................................... 7-64
ALTER RESOURCE COST............................................................................................................. 7-68
ALTER ROLE..................................................................................................................................... 7-71
ALTER ROLLBACK SEGMENT................................................................................................... 7-73
ALTER SEQUENCE.......................................................................................................................... 7-76
ALTER SESSION.............................................................................................................................. 7-78
ALTER SNAPSHOT......................................................................................................................... 7-93
ALTER SNAPSHOT LOG............................................................................................................... 7-94
ALTER SYSTEM............................................................................................................................... 7-95
ALTER TABLE................................................................................................................................. 7-113
ALTER TABLESPACE.................................................................................................................... 7-164
ALTER TRIGGER........................................................................................................................... 7-171
ALTER TYPE.................................................................................................................................... 7-173
ALTER USER................................................................................................................................... 7-179
ALTER VIEW................................................................................................................................... 7-183
ANALYZE......................................................................................................................................... 7-185
ASSOCIATE STATISTICS............................................................................................................ 7-194
AUDITsql_statements................................................................................................................... 7-197
AUDITschema_objects.................................................................................................................. 7-205
CALL................................................................................................................................................. 7-210
COMMENT...................................................................................................................................... 7-212
COMMIT.......................................................................................................................................... 7-214
constraint_clause............................................................................................................................. 7-217
CREATE CLUSTER........................................................................................................................ 7-236
CREATE CONTEXT....................................................................................................................... 7-243
CREATE CONTROLFILE.............................................................................................................. 7-245
CREATE DATABASE..................................................................................................................... 7-249
CREATE DATABASE LINK.......................................................................................................... 7-255
v
CREATE DIMENSION.................................................................................................................. 7-259
CREATE DIRECTORY................................................................................................................... 7-264
CREATE FUNCTION..................................................................................................................... 7-266
CREATE INDEX.............................................................................................................................. 7-273
CREATE INDEXTYPE.................................................................................................................... 7-291
CREATE JAVA.................................................................................................................................. 7-293
CREATE LIBRARY......................................................................................................................... 7-298
CREATE MATERIALIZED VIEW / SNAPSHOT..................................................................... 7-300
CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG................................................. 7-314
CREATE OPERATOR..................................................................................................................... 7-320
CREATE OUTLINE......................................................................................................................... 7-323
CREATE PACKAGE....................................................................................................................... 7-325
CREATE PACKAGE BODY.......................................................................................................... 7-328
CREATE PROCEDURE.................................................................................................................. 7-333
CREATE PROFILE.......................................................................................................................... 7-338
CREATE ROLE................................................................................................................................ 7-344
CREATE ROLLBACK SEGMENT............................................................................................... 7-346
CREATE SCHEMA......................................................................................................................... 7-348
CREATE SEQUENCE..................................................................................................................... 7-350
CREATE SNAPSHOT.................................................................................................................... 7-354
CREATE SNAPSHOT LOG.......................................................................................................... 7-355
CREATE SYNONYM...................................................................................................................... 7-356
CREATE TABLE.............................................................................................................................. 7-359
CREATE TABLESPACE................................................................................................................. 7-394
CREATE TEMPORARY TABLESPACE...................................................................................... 7-399
CREATE TRIGGER........................................................................................................................ 7-401
CREATE TYPE................................................................................................................................. 7-411
CREATE TYPE BODY.................................................................................................................... 7-421
CREATE USER................................................................................................................................. 7-425
CREATE VIEW................................................................................................................................ 7-430
DELETE............................................................................................................................................. 7-438
DISASSOCIATE STATISTICS..................................................................................................... 7-444
DROP CLUSTER............................................................................................................................. 7-446
DROP CONTEXT............................................................................................................................ 7-448
DROP DATABASE LINK.............................................................................................................. 7-449
vi
DROP DIMENSION...................................................................................................................... 7-450
DROP DIRECTORY....................................................................................................................... 7-451
DROP FUNCTION......................................................................................................................... 7-452
DROP INDEX.................................................................................................................................. 7-454
DROP INDEXTYPE........................................................................................................................ 7-456
DROP JAVA..................................................................................................................................... 7-457
DROP LIBRARY............................................................................................................................. 7-458
DROP MATERIALIZED VIEW / SNAPSHOT......................................................................... 7-459
DROP MATERIALIZED VIEW LOG / SNAPSHOT LOG..................................................... 7-461
DROP OPERATOR......................................................................................................................... 7-463
DROP OUTLINE............................................................................................................................ 7-464
DROP PACKAGE........................................................................................................................... 7-465
DROP PROCEDURE...................................................................................................................... 7-467
DROP PROFILE.............................................................................................................................. 7-468
DROP ROLE.................................................................................................................................... 7-469
DROP ROLLBACK SEGMENT................................................................................................... 7-470
DROP SEQUENCE......................................................................................................................... 7-471
DROP SNAPSHOT........................................................................................................................ 7-472
DROP SNAPSHOT LOG.............................................................................................................. 7-473
DROP SYNONYM.......................................................................................................................... 7-474
DROP TABLE.................................................................................................................................. 7-475
DROP TABLESPACE..................................................................................................................... 7-477
DROP TRIGGER............................................................................................................................ 7-479
DROP TYPE..................................................................................................................................... 7-480
DROP TYPE BODY........................................................................................................................ 7-482
DROP USER.................................................................................................................................... 7-483
DROP VIEW.................................................................................................................................... 7-485
EXPLAIN PLAN.............................................................................................................................. 7-486
filespec............................................................................................................................................... 7-490
GRANTsystem_privileges_and_roles......................................................................................... 7-493
GRANTobject_privileges.............................................................................................................. 7-505
INSERT............................................................................................................................................. 7-512
LOCK TABLE................................................................................................................................... 7-520
NOAUDITsql_statements............................................................................................................ 7-523
NOAUDITschema_objects........................................................................................................... 7-525
vii
RENAME.......................................................................................................................................... 7-527
REVOKEsystem_privileges_and_roles....................................................................................... 7-529
REVOKEschema_object_privileges............................................................................................. 7-532
ROLLBACK...................................................................................................................................... 7-537
SAVEPOINT..................................................................................................................................... 7-539
SELECT and Subqueries............................................................................................................... 7-541
SET CONSTRAINT(S)................................................................................................................... 7-568
SET ROLE......................................................................................................................................... 7-570
SET TRANSACTION..................................................................................................................... 7-572
storage_clause.................................................................................................................................. 7-575
TRUNCATE...................................................................................................................................... 7-581
UPDATE............................................................................................................................................ 7-584
A Syntax Diagrams
B Oracle and Standard SQL
Conformance with Standard SQL................................................................................................. B - 1
Oracle Extensions to Standard SQL............................................................................................. B - 5
C Oracle Reserved Words
viii
Send Us Your Comments
SQL Reference, Release 8.1.5
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.
n Did you find any errors?
n Is the information clearly presented?
n Do you need more information? If so, where?
n Are the examples correct? Do you need more examples?
n What features did you like most about this manual?
If you find any errors or have any other suggestions for improvement, please indicate the chapter,
section, and page number (if available). Please send your comments to:
Server Technologies Documentation Manager
Oracle Corporation
500 Oracle Parkway
Redwood Shores, CA 94065
Fax: [phone]
or e-mail comments to the Information Development department at the following e-mail address:
[email protected]
ix
x