Oracle 9i: The Complete Reference

1214 Pages·2002·22.718 MB·English

by  Kevin Loney, George Koch

Color profile: Generic CMYK printer pOroRfAiCleLE Series TIGHT/ Oracle9i: The Complete Reference / Loney, Koch / 222521-1/ Front Matter
Composite Default screen Blind FolioFM:i
Oracle9i:
The Complete
Reference
Kevin Loney
George Koch
And the Experts at TUSC
McGraw-Hill/Osborne
New York Chicago San Francisco
Lisbon London Madrid Mexico City Milan
New Delhi San Juan Seoul Singapore Sydney Toronto
P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp
Friday, July 19, 2002 4:10:30 PM
Color profile: Generic CMYK printer pOroRfAiCleLE Series TIGHT/ Oracle9i: The Complete Reference / Loney, Koch / 222521-1/ Front Matter
Composite Default screen Blind FolioFM:ii
McGraw-Hill/Osborne
2600 Tenth Street
Berkeley, California 94710
U.S.A.
To arrange bulk purchase discounts for sales promotions, premiums, or fund-raisers, please contact
McGraw-Hill/Osborneat the above address. For information on translations or book distributors
outside the U.S.A., please see the International Contact Information page immediately following the
index of this book.
Oracle 9i: The Complete Reference
Copyright © 2002 by The McGraw-Hill Companies, Inc. (Publisher). All rights reserved. Printed in
the United States of America. Except as permitted under the Copyright Act of 1976, no part of this
publication may be reproduced or distributed in any form or by any means, or stored in a database
or retrieval system, without the prior written permission of Publisher.
Oracle is a registered trademark and Oracle9iis a trademark or registered trademark of Oracle
Corporation.
1234567890 DOC DOC 0198765432
Book p/n 0-07-222707-9 and CD p/n 0-07-222708-7
parts of
ISBN 0-07-222521-1
Publisher Copy Editor
Brandon A. Nordin Margaret Berson
Vice President & Associate Publisher Proofreader
Scott Rogers Cheryl Abel
Acquisitions Editor Indexer
Lisa McClain James Minkin
Project Editor Computer Designers
LeeAnn Pickrell Tabitha M. Cagan, Lucie Ericksen
Acquisitions Coordinator Illustrators
Athena Honore Michael Mueller, Lyssa Wald
Technical Editor Cover Series Design
Bob Bryla Damore Johann Design, Inc.
This book was composed with Corel VENTURA™ Publisher.
Information has been obtained by Publisher from sources believed to be reliable. However, because of the possibility of human or
mechanical error by our sources, Publisher, or others, Publisher does not guarantee to the accuracy, adequacy, or completeness of
any information included in this work and is not responsible for any errors or omissions or the results obtained from the use of such
information.
Oracle Corporation does not make any representations or warranties as to the accuracy, adequacy or completeness of any information
contained in this Work, and is not responsible for any errors or omissions.
P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp
Friday, July 19, 2002 4:10:31 PM
Color profile: Generic CMYK printer pOroRfAiCleLE Series TIGHT/ Oracle9i: The Complete Reference / Loney, Koch / 222521-1/ Front Matter
Composite Default screen Blind FolioFM:iii
To my parents, and to Sue, Emily, Rachel, and Jane
—K.L.
To Elwood Brant, Jr. (Woody), 1949–1990
—G.K.
P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp
Friday, July 19, 2002 4:10:31 PM
Color profile: Generic CMYK printer pOroRfAiCleLE Series TIGHT/ Oracle9i: The Complete Reference / Loney, Koch / 222521-1/ Front Matter
Composite Default screen Blind FolioFM:iv
About the Authors
Kevin Loneyis a senior management technical consultant with
TUSC (http://www.tusc.com), an Oracle-focused consultancy
headquartered in Chicago. He is an expert in the administration,
tuning, security, recovery, design, and development of Oracle
databases and applications. An Oracle DBA and developer since
1987, he is the primary author of numerous books, including
Oracle9i DBA Handbook,Oracle9i Instant Scripts, andOracle8
Advanced Tuning and Administration, all published by Oracle
Press. He is a frequent presenter at local and international Oracle
user groups.
George Koch is a leading authority on relational database
applications. A popular speaker and widely published author, he is
also the creator of THESIS, the securities trading, accounting, and
portfolio management system that was the first major commercial
applications product in the world to employ a relational database
(Oracle) and provide English language querying to its users. He is a
former senior vice president of Oracle Corporation.
P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp
Friday, July 19, 2002 4:10:31 PM
Color profile: Generic CMYK printer pOroRfAilCeLE Series TIGHT/ Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter55
Composite Default screen Blind Folio55:v
Contents At a Glance
PART I
Critical Database Concepts
1 Sharing Knowledge and Success . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2 The Dangers in a Relational Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3 The Basic Parts of Speech in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
4 The Basics of Object-Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
5 Introduction to Web-Enabled Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
PART II
SQL and SQL*PLUS
6 Basic SQL*PLUS Reports and Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
7 Getting Text Information and Changing It . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
8 Playing the Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
9 Dates: Then, Now, and the Difference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
10 Conversion and Transformation Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
11 Grouping Things Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
12 WhenOneQueryDependsuponAnother . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
13 Some Complex Possibilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
14 Building a Report in SQL*PLUS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
15 Changing Data: insert, update, merge, and delete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
16 Advanced Use of Functions and Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
17 DECODE and CASE: if, then, and else in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
18 Creating, Dropping, and Altering Tables and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
19 By What Authority? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
20 Changing the Oracle Surroundings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
v
P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp
Friday, July 19, 2002 4:10:32 PM
Color profile: Generic CMYK printer pOroRfAiCleLE Series TIGHT/ Oracle9i: The Complete Reference / Loney, Koch / 222521-1/ Front Matter
Composite Default screen Blind FolioFM:vi
vi Oracle9i: The Complete Reference
21 Using SQL*Loader to Load Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
22 Accessing Remote Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
23 Using Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423
24 Using Oracle Text for Text Searches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
25 Using External Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465
26 Using Flashback Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479
PART III
PL/SQL
27 An Introduction to PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489
28 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
29 Procedures, Functions, and Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529
PART IV
Object-Relational Databases
30 Implementing Types, Object Views, and Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 551
31 Collectors (Nested Tables and Varying Arrays) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567
32 Using Large Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 581
33 Advanced Object-Oriented Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607
PART V
Java in Oracle
34 AnIntroductiontoJava . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627
35 JDBC and SQLJ Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645
36 Java Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 663
PART VI
Hitchhiker’s Guides
37 The Hitchhiker’s Guide to the Oracle9i Data Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . 673
38 The Hitchhiker’s Guide to the Oracle Optimizer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 721
39 The Hitchhiker’s Guide to Oracle9iAS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 769
40 The Hitchhiker’s Guide to Database Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 791
41 TheHitchhiker’sGuidetoXMLinOracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 827
PART VII
Alphabetical Reference
Alphabetical Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 843
P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp
Friday, July 19, 2002 4:10:32 PM
Color profile: Generic CMYK printer pOroRfAiCleLE Series TIGHT/ Oracle9i: The Complete Reference / Loney, Koch / 222521-1/ Front Matter
Composite Default screen Blind FolioFM:vii
Acknowledgments
T
his book is dedicated to my family, who have allowed me the time to write it.
Thank you for your patience and support and love.
Beyond that, this book is dedicated to the memory of two people: Stephen Jay
Gould and Matthew Horning. Stephen Jay Gould inspired me to be a technical
writer—his steady growth as a writer and his clarity of thought and expression
made me believe this kind of writing was a possibility. He passed away as this book was being
finished, and the world is poorer for his passing.
Matthew Horning was an Oracle DBA I worked with for several weeks during the summer
of2001ontheupperfloorsof#1WorldTradeCenterinNewYorkCity.Hehadthemisfortuneof
being in the office early on 9/11. His coworkers were inspiring to work with during the recovery
efforts that followed. In his obituary, Matt’s family asked that donations in his memory be given
to Heifer International (http://www.heifer.org). If an act of compassion can come out of such
destruction, then there may always be hope. As Gould noted, “Ordinary kindness trumps
paroxysmal evil by at least a million events to one.”
This book is the product of many hands, and countless hours from many people. My thanks
go out to all those who helped, whether through their comments, feedback, edits, or suggestions.
For additional information about the book, see the publisher’s site (http://www.osborne.com) and
my site (http://www.kevinloney.com). Additional articles and presentations can be found on the
company site at http://www.tusc.com.
Thanks to all of my colleagues at TUSC:
(cid:1)
Tothecontributorsandreviewersthere,includingBradBrown,JayUrban,andMikeHolder.
(cid:1)
To the exemplary management, including Jake Van der Vort, Rich Niemiec, Joe Trezzo,
Brad Brown, and others. It’s a delight to work with an executive team who understands
therequirementsofthiskindofundertakingandwhosharesacommitmenttoprofessional
altruism. Thanks to the rest of the management team there who actively pursue the
professional traits TUSC values.
(cid:1)
TomypeersatTUSC,includingMikeAult,BillCallahan,PatrickCallahan,HollyClawson,
Judy Corley, Mark Greenhalgh, Andy Hamilton, Mike Killough, Allen Peterson, Randy
Swanson, Bob Taylor, Bob Yingst, and many others for their insights and contributions.
vii
P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp
Friday, July 19, 2002 4:10:32 PM
Color profile: Generic CMYK printer pOroRfAiCleLE Series TIGHT/ Oracle9i: The Complete Reference / Loney, Koch / 222521-1/ Front Matter
Composite Default screen Blind FolioFM:viii
viii Oracle9i: The Complete Reference
Special thanks to Bob Bryla, who served as technical editor for this edition. His thoroughness,
corrections, and suggestions were greatly appreciated. Imagine being a technical editor on a
1400 page book covering such a range of content and you can begin to appreciate Bob’s task—
then do it while working on my schedule!
Thanks to my colleagues and friends, including Eyal Aronoff, John Beresniewicz, Steve
Bobrowski, Rachel Carmichael, Steven Feuerstein, Mike McDonnell, Marlene Theriault, Mike
Janesch, Craig Warman, and Vinny Smith. This book has benefited from the knowledge they
have shared, and I have benefited from their friendship and guidance.
Thanks to the folks at McGraw-Hill/Osborne who guided this product through its stages:
Scott Rogers, LeeAnn Pickrell, Athena Honore, Lisa McClain, and Jeremy Judson, and the others
at Osborne with whom I never directly worked. Thanks also to the Oracle component of Oracle
Press. This book would not have been possible without the earlier excellent work of George Koch
and Robert Muller.
Thanks to the writers and friends along the way: Jerry Gross; Jan Riess; Robert Meissner;
Marie Paretti; Br. Declan Kane, CFX; Br. William Griffin, CFX; Chris O’Neill; Cheryl Bittner;
Bill Fleming; and Mike Restuccia.
Thanks to the First State Oracle User Group board (Pete Silva, Phil Stewart, Earl Shaffer, and
Lori Kaupas) for its support (http://www.fsoug.org).
Special thanks to Sue, Emily, Rachel, Jane, and the rest of the home team. As always, this has
been a joint effort.
—Kevin Loney
P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp
Friday, July 19, 2002 4:10:32 PM
Color profile: Generic CMYK printer pOroRfAiCleLE Series TIGHT/ Oracle9i: The Complete Reference / Loney, Koch / 222521-1/ Front Matter
Composite Default screen Blind FolioFM:ix
Preface
The Intriguing History of This Book
I
firstencounteredOraclein1982,intheprocessofevaluatingdatabasemanagement
systemsforamajorcommercialapplicationthatmycompanywaspreparingtodesign
andbuild.Atitsconclusion,ourevaluationwascharacterizedbyComputerWorldas
the single-most “grueling” study of DBMSs that had ever been conducted. The study
was so tough on the vendors whose products we examined that word of it made the
pressasfarawayasNewZealandandpublicationsasfarafieldastheChristianScienceMonitor.
We began the study with 108 candidate companies, then narrowed the field to sixteen
finalists, including most of the major database vendors of the time, and all types of databases:
network, hierarchical, relational, and others. After the rigorous final round of questions, two
of the major vendors participating asked that the results of the study of their products never be
published. A salesman from a third vendor quit his job at the end of one of the sessions. We
knew how to ask tough questions.
Oracle, known then as Relational Software, Inc., had fewer than 25 employees at the time,
and only a few major accounts. Nevertheless, when the study was completed, we announced
Oracle as the winner. We declared that Oracle was technically the best product on the market,
and that the management team at RSI looked capable enough to carry the company forward
successfully. Our radical proclamation was made at a time when few people even knew what
the term relational meant, and those who did had very few positive things to say about it. Many
IS executives loudly criticized our conclusions and predicted that Oracle and the relational
database would go nowhere.
Oracle today is the largest database company, and the second largest software company in
the world. The relational database is now the world standard.
Koch Systems Corporation, the company I owned and ran at the time, became Oracle’s first
Valued Added Reseller. We developed the world’s first major commercial relational application,
a securities trading and accounting system called THESIS. This product was used by major banks
and corporations to manage their investment portfolios. Even IBM bought THESIS, and it allowed
Oracle to be installed at IBM headquarters in spite of vigorous internal opposition. After all, IBM
was the leading database company at the time, with IMS and DB2 as their flagship products.
Oracle was continuing to refine its young product, to understand the kinds of features and
functionalitythatwouldmakeitproductiveandusefulinthebusinessworld,andourdevelopment
efforts at Koch Systems contributed to that refinement. Some of Oracle’s features were the direct
ix
P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp
Friday, July 19, 2002 4:10:32 PM
Color profile: Generic CMYK printer pOroRfAiCleLE Series TIGHT/ Oracle9i: The Complete Reference / Loney, Koch / 222521-1/ Front Matter
Composite Default screen Blind FolioFM:x
x Oracle9i: The Complete Reference
results of requests that we made of Oracle’s developers, and our outspoken advocacy of an
end-user bias in application design and naming conventions has influenced a generation of
programmers who learned Oracle in our shop or read articles which we published.
All of this intimate involvement with the development and use of Oracle led us to an early
and unmatched expertise with the product and its capabilities. Since I have always loved sharing
discoveries and knowledge—to help shorten the learning time necessary with new technologies
and ideas, and save others the cost of making the same mistakes I did—I decided to turn what
we’d learned into a book.
Oracle:TheCompleteReferencewasconceivedin1988topulltogetherallofthefundamental
commands and techniques used across the Oracle product line, as well as give solid guidance
inhow to develop applications using Oracle and SQL. Part I of the book was aimed both at
developersandend-users,sothattheycouldshareacommonlanguageandunderstandingduring
the application development process: developers and end users working side by side—a wild
concept when the book was first conceived.
Linda Allen, a respected literary agent in San Francisco, introduced me to Liz Fisher, then the
editor at McGraw-Hill/Osborne. Liz liked the idea very much. Contracts were drawn, and the first
editionwasscheduledtobereleasedin1989.Butanow-departedseniorexecutiveatMcGraw-Hill
heard of the project and instantly canceled its development, pronouncing that Oracle is a flash in
thepan.Itisgoingnowhere.Ayearlater,whenOracleCorporationhadagaindoubledinsizeand
theseniorexecutivewasgone,theeffortwasrestarted,andthefirsteditionfinallyarrivedin1990.
Almost immediately, it became the No. 1 book in its category, a position it has maintained
for over a decade.
In July of 1990, I was hired by Oracle to run its Applications Division. I became senior vice
presidentofthecompanyandguidedthedivision(withalotoftalentedhelp)toworldwidesuccess.
While at Oracle, I also introduced McGraw-Hill/Osborne to Oracle senior management, and
after opposition from an Oracle vice president who didn’t see any value in the idea (he’s no
longer with Oracle), Oracle Press was born.
Oracle Press is now the leading publisher of Oracle-based reference manuals in the world.
In 1992, Bob Muller, a former developer at both Koch Systems and Oracle, took over
responsibilities for technical updates to the book, as my duties at Oracle precluded any more
than editorial review of changes. This producedOracle7: The Complete Reference. This was
Bob’s first published book, and he has since gone on to write several other popular books on
development and database design.
In1994,IleftOracletofulfillalong-helddesire—fulltimeministry—andtodayI’mthepastor
of Church of the Resurrection (http://www.resurrection.org) in West Chicago, Illinois. I continue
towriteinpublicationsasdiverseastheWallStreetJournalandChristianityToday,andI’ve
recentlypublishedabookinEngland,TheCountryParson’sAdvicetoHisParishioner,from
Monarch Books. I also sit on the board of directors of Apropos, a leading call center applications
company, but I no longer work in Oracle application development.
Also in 1994, Kevin Loney, a highly respected independent Oracle consultant and author
(http://www.kevinloney.com), took over the updating and rewriting responsibilities for the third
edition of the book, and has continued ever since. He has contributed major new sections (such
as the Hitchhiker’s Guides, the PL/SQL, Java, and ORDBMS sections, among others), and fully
integrated new Oracle product features into all sections of the book. He has also integrated many
readers’commentsintothestructureandcontentofthebook,makingitscurrentformtheproduct
of both its readers and its authors. Those efforts have allowedOracle: The Complete Referenceto
P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp
Friday, July 19, 2002 4:10:33 PM