Oracle 8i : the complete reference

1234 Pages·2000·17.386 MB·English

by  Kevin Loney, George Koch

Color profile: Disabled ORACLE Series / Oracle 8i: TCR / Loney / 2364-8 / Front Matter
Composite Default screen Blind FolioFM:i
Oracle8i:
The Complete
Reference
Kevin Loney
George Koch
Osborne/McGraw-Hill
Berkeley New York St. Louis San Francisco
Auckland Bogotá Hamburg London Madrid
Mexico City Milan Montreal New Delhi Panama City
Paris São Paulo Singapore Sydney Tokyo Toronto
P:\010Comp\Oracle8\364-8\fm-CD.vp
Thursday, April 13, 2000 3:50:55 PM
Color profile: Disabled ORACLE Series / Oracle 8i: TCR / Loney / 2364-8 / Front Matter
Composite Default screen Blind FolioFM:ii
Osborne/McGraw-Hill
2600 Tenth Street
Berkeley, California 94710
U.S.A.
For information on translations or book distributors outside the U.S.A., or to arrange
bulk purchase discounts for sales promotions, premiums, or fund-raisers, please
contact Osborne/McGraw-Hillat the above address.
Oracle8i: The Complete Reference
Copyright © 2000 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 Oracle8iis a trademark or registered trademark
of Oracle Corporation.
1234567890 DOC DOC 019876543210
Book P/N 0-07-212362-1 and CD P/N 0-07-212363-X
parts of
ISBN 0-07-212364-8
Publisher Proofreader
Brandon A. Nordin Mike McGee
Associate Publisher and Indexer
Editor-in-Chief David Heiret
Scott Rogers
Computer Designer
Acquisitions Editor Jani Beckwith
Jeremy Judson Michelle Galicia
Roberta Steele
Project Editor
Janet Walden Illustrator
Michael Mueller
Acquisitions Coordinator
Beth Young
Monika Faltiss
Series Design
Technical Editor
Jani Beckwith
Leslie Tierstein
Copy Editor
William McManus
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
ofhumanormechanicalerrorbyoursources,Publisher,orothers,Publisherdoesnotguaranteetotheaccuracy,
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.
P:\010Comp\Oracle8\364-8\fm-CD.vp
Thursday, April 13, 2000 3:50:55 PM
Color profile: Disabled ORACLE Series / Oracle 8i: TCR / Loney / 2364-8 / Front Matter
Composite Default screen Blind FolioFM:iii
To my parents, and to Sue, Emily, and Rachel
—K.L.
To Elwood Brant, Jr. (Woody), 1949-1990
—G.K.
P:\010Comp\Oracle8\364-8\fm-CD.vp
Thursday, April 13, 2000 3:50:55 PM
Color profile: Disabled ORACLE Series / Oracle 8i: TCR / Loney / 2364-8 / Front Matter
Composite Default screen Blind FolioFM:iv
About the Authors
Kevin Loney, a veteran Oracle developer and DBA, is the author of the
best-sellingOracle8i DBA Handbookand coauthor ofOracle8 Advanced Tuning
and AdministrationandOracle SQL & PL/SQL Annotated Archives. An independent
consultant, he frequently makes presentations at Oracle conferences and contributes
toORACLE Magazine. He can be found online at http://www.kevinloney.com, and
is the editor for the database-related search engine at http://www.lonyx.com.
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\364-8\fm-CD.vp
Thursday, April 13, 2000 3:50:55 PM
Color profile: Disabled ORACLE Series/ Oracle 8i: TCR / Loney / 2364-8 / Chapter1
Composite Default screen Blind Folio1:v
Contents at a Glance
PREFACE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii
ACKNOWLEDGMENTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x
INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
PART I
Critical Database Concepts
1 Sharing Knowledge and Success . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2 The Dangers in a Relational Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
3 The Basic Parts of Speech in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
4 The Basics of Object-Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
5 Introduction to Web-Enabled Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
PART II
SQL and SQL*PLUS
6 Basic SQLPLUS Reports and Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
7 Getting Text Information and Changing It . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
8 Playing the Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
9 Dates: Then, Now, and the Difference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
10 Conversion and Transformation Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
11 Grouping Things Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
12 When One Query Depends upon Another . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
13 Some Complex Possibilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
14 Building a Report in SQLPLUS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
15 Changing Data: insert, update, and delete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
16 Advanced Use of Functions and Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
17 DECODE: Amazing Power in a Single Word . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
18 Creating, Dropping, and Altering Tables and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
19 By What Authority? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
20 Changing the Oracle Surroundings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417
21 Using SQL*Loader to Load Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
v
P:\010Comp\Oracle8\364-8\fm-CD.vp
Thursday, April 13, 2000 3:50:56 PM
Color profile: Disabled ORACLE Series / Oracle 8i: TCR / Loney / 2364-8 / Front Matter
Composite Default screen Blind FolioFM:vi
vi Oracle8i: The Complete Reference
22 Accessing Remote Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453
23 Snapshots and Materialized Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469
24 Using interMedia Text for Text Searches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491
PART III
PL/SQL
25 An Introduction to PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511
26 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533
27 Procedures, Functions, and Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 549
PART IV
Object-Relational Databases
28 Implementing Types, Object Views, and Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573
29 Collectors (Nested Tables and Varying Arrays) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593
30 Using Large Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615
31 Advanced Object-Oriented Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645
PART V
Java in Oracle
32 An Introduction to Java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667
33 JDBC and SQLJ Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 685
34 Java Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 705
PART VI
Hitchhiker’s Guides
35 The Hitchhiker’s Guide to the Oracle8i Data Dictionary . . . . . . . . . . . . . . . . . . . . . . . 717
36 The Hitchhiker’s Guide to the Oracle Optimizer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 775
37 A Brief Introduction to WebDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 831
38 Beginner’s Guide to Database Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 841
PART VII
Designing for Performance
39 Good Design Has a Human Touch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 885
40 Performance and Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 897
41 The Ten Commandments of Humane Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 909
PART VIII
Alphabetical Reference
Alphabetical Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 921
PART IX
Appendix
A Tables Used in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1223
About the CD-ROM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1313
P:\010Comp\Oracle8\364-8\fm-CD.vp
Thursday, April 13, 2000 3:50:56 PM
Color profile: Disabled ORACLE Series / Oracle 8i: TCR / Loney / 2364-8 / Front Matter
Composite Default screen Blind FolioFM:vii
Preface
The Intriguing History of This Book
I
first encountered Oracle in 1982, in the process of evaluating database
management systems for a major commercial application that my company
was preparing to designandbuild.Atitsconclusion,ourevaluationwas
characterizedinComputerWorldasthe single-most “grueling” study of DBMSs
that had ever been conducted. The study was sotoughonthevendorswhose
productsweexaminedthatwordofitmadethepressasfar away as New Zealand, and
publications as far afield as theChristian Science Monitor.
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,
twoofthemajorvendorsparticipatingaskedthattheresultsofthestudyoftheirproducts
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
companyforwardsuccessfully.Ourradicalproclamationwasmadeatatimewhenfewpeople
even knew what the term “relational” meant, and those who did had very few positive
things to say about it. Many IS exectives loudly criticized our conclusions and predicted that
Oracle and the relational database would go nowhere.
vii
P:\010Comp\Oracle8\364-8\fm-CD.vp
Thursday, April 13, 2000 3:50:57 PM
Color profile: Disabled ORACLE Series / Oracle 8i: TCR / Loney / 2364-8 / Front Matter
Composite Default screen Blind FolioFM:viii
viii Oracle8i: The Complete Reference
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,anditallowedOracletobeinstalledatIBMheadquartersinspiteofvigorousinternal
opposition. After all, IBM was the leading database company at the time, with IMS and
DB2 as their flagship products.
Oraclewascontinuingtorefineitsyoungproduct,tounderstandthekindsoffeatures
andfunctionalitythatwouldmakeitproductiveandusefulinthebusinessworld,andour
developmenteffortsatKochSystemscontributedtothatrefinement.SomeofOracle’s
featureswerethedirectresultsofrequeststhatwemadeofOracle’sdevelopers,andour
outspokenadvocacyofanend-userbiasinapplicationdesignandnamingconventions
has influenced a generation of programmers who learned Oracle in our shop or read articles
whichwepublished.
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:TheCompleteReferencewasconceivedin1988topulltogetherallofthe
fundamental commands and techniques used across the Oracle product line, as well as give
solid guidance in how to develop applications using Oracle and SQL. Part I of the book
was aimed both at developers and end-users, so that they could share a common language
and understanding during 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 Osborne/McGraw-Hill. Liz liked the idea very much. Contracts were drawn, and
the first edition was scheduled to be released in 1989. But a now-departed senior executive
at McGraw-Hill heard of the project and instantly canceled its development, pronouncing
that “Oracle is a flash in the pan. It is going nowhere.” A year later, when Oracle Corporation
had again doubled in size and the senior executive was gone, the effort was restarted, and
the first edition finally arrived in 1990.
Almostimmediately,itbecametheNo.1bookinitscategory,apositionithasmaintained
for a decade.
In July of 1990, I was hired by Oracle to run its Applications Division. I became senior
vicepresidentofthecompanyandguidedthedivision(withalotoftalentedhelp)toworldwide
success.WhileatOracle,IalsointroducedOsborne/McGraw-HilltoOracleseniormanagement,
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.
OraclePressisnowtheleadingpublisherofOracle-basedreferencemanualsintheworld.
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.
P:\010Comp\Oracle8\364-8\fm-CD.vp
Thursday, April 13, 2000 3:50:57 PM
Color profile: Disabled ORACLE Series / Oracle 8i: TCR / Loney / 2364-8 / Front Matter
Composite Default screen Blind FolioFM:ix
Preface ix
In 1994 I left Oracle to fulfill a long-held desire—full-time ministry—and today I’m the
pastor of Church of the Resurrection (http://www.resurrection.org) in West Chicago, Illinois.
I continue to write in publications as diverse as theWall Street JournalandChristianity
Today, and I’ve recently published a book in England,The Country Parson’s Advice to His
Parishioner, 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 Guide”), and fully integrated new Oracle product features
intoallsectionsofthebook.Hehasalsointegratedmanyreaders’commentsintothestructure
andcontentofthebook,makingitscurrentformtheproductofbothitsreadersandits
authors. Those efforts have allowedOracle: The Complete Referenceto stay at the top of
its field and continue to be the single-most comprehensive guide to Oracle—still unmatched
in range, content, and authority. I am a real fan of Kevin’s and am most impressed by his
knowledge and thoroughness.
Oracle: The Complete Referenceis now available in eight languages, and is found on the
desks of developers and Oracle product users all over the world. Not only has it been No. 1
in its category (with two editions out, it once was both No. 1 and No. 4), it has also been
regularly in the top 100 ofallbooks sold through Amazon.com. At one point it was the No. 7
best-sellingbookofallbookssoldinBrazil!Itsreputationandenduringsuccessareunparalleled
in its marketplace.
LikeOracleitself,thebookhassurvivedandprosperedinspiteoftherecurringpredictions
of failure from many quarters. Perhaps this brief history can be an encouragement to others
who face opposition but have a clear vision of what is needed in the years ahead.
As Winston Churchill said, “Never give in, never give in, never give in—in nothing great
or small, large or petty—never give in except to convictions of honor and good sense.”
George Byron Koch
[email protected]
Wheaton, Illinois
April, 2000
P:\010Comp\Oracle8\364-8\fm-CD.vp
Thursday, April 13, 2000 3:50:57 PM
Color profile: Disabled ORACLE Series / Oracle 8i: TCR / Loney / 2364-8 / Front Matter
Composite Default screen Blind FolioFM:x
Acknowledgments
T
his 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). For a database of links related to Oracle topics
across the Web, see http://www.lonyx.com.
Special thanks to Leslie Tierstein, who served as technical editor for this edition. Her
thoroughness and advice is greatly appreciated. If there is an error in here somewhere, it must
have happened after Leslie read it.
Thanks to my colleagues and friends, including Eyal Aronoff, John Beresniewicz, Steve
Bobrowski, Rachel Carmichael, Steven Feuerstein, Mike McDonnell, Marlene Theriault, and
Craig Warman. This book has benefited from the knowledge they have shared, so be sure
and thank them when you see them at conferences.
Thanks to the folks at Osborne/McGraw-Hill who guided this product through its stages:
Scott Rogers, Janet Walden, Monika Faltiss, and Jeremy Judson, and the others at Osborne
with whom I never directly worked. Thanks also to the “Oracle” component of Oracle Press,
including Julie Gibbs and Marsha Bazley. 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 the PSCI team.
Special thanks to Sue, Emily, and Rachel and the rest of the home team. As always, this
has been a joint effort.
—Kevin Loney
x
P:\010Comp\Oracle8\364-8\fm-CD.vp
Thursday, April 13, 2000 3:50:57 PM