by Michael Rosenblum and Dr. Paul Dorsey
Oracle
®
PL/SQL
FOR
DUMmIES
‰
01_599577 ffirs.qxp 5/1/06 12:08 PM Page i
01_599577 ffirs.qxp 5/1/06 12:08 PM Page iv
by Michael Rosenblum and Dr. Paul Dorsey
Oracle
®
PL/SQL
FOR
DUMmIES
‰
01_599577 ffirs.qxp 5/1/06 12:08 PM Page i
Oracle
®
PL/SQL For Dummies
®
Published by
Wiley Publishing, Inc.
111 River Street
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or

For technical support, please visit
www.wiley.com/techsupport
.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may
not be available in electronic books.
Library of Congress Control Number: 2006922426
ISBN-13: 978-0-7645-9957-6
ISBN-10: 0-7645-9957-7
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
1O/RX/QV/QW/IN
01_599577 ffirs.qxp 5/1/06 12:08 PM Page ii
About the Authors
Michael Rosenblum is originally from Kremenchuk, Ukraine. In 2000, he moved
to the United States, where he lives with his family in Edison, New Jersey. He
works as a Development DBA at Dulcian, Inc. Michael is responsible for system
tuning and application architecture. He also supports Dulcian developers
by writing complex PL/SQL routines and researching new features. He is a
frequent presenter at various regional and national Oracle user group confer-
ences. In his native Ukraine, he received the scholarship of the President of
Ukraine, a Masters Degree in Information Systems, and a Diploma with Honors
from the Kiev National University of Economics, Ukraine.
Dr. Paul Dorsey is the founder and President of Dulcian, Inc. (www.dulcian.
com), an Oracle consulting firm that specializes in business rules-based
Oracle Client-Server and Web custom application development. He is the
chief architect of Dulcian’s Business Rules Information Manager (BRIM
®
) tool.
Paul is the co-author of seven Oracle Press books that have been translated
into nine languages: Oracle JDeveloper 10g Handbook, Oracle9i JDeveloper

number of small “bugs” she discovered in the original drafts). And, last but
not least, love and special thanks to my wife Dora for withstanding the added
pressure on her while I was writing this book.
Dr. Paul Dorsey: I would first like to acknowledge my co-author Michael
(“Misha”) Rosenblum. It is a joy to work with someone possessing such drive
and intellect. His unwillingness to be sloppy in his code and thinking have
given not only this book, but all his work, an aspect of excellence all too
unusual in this industry. I would also like to thank Caryl Lee Fisher (our unac-
knowledged “co-author”). Caryl Lee kept Misha and me on track and helped
wordsmith the entire manuscript. This is my eighth such collaboration
involving Caryl Lee, and I can safely say that I am not sure whether I ever
would have published even my first book without her assistance. She acted
as the intermediary between the excellent editors at Wiley and the authors,
thereby averting virtually certain bloodshed. She helped to foster the illusion
that we are very easy authors to work with. Leslie Tierstein provided her
always impeccable technical edits. I have worked with her on a number of
projects, and she provides many valuable contributions to the finished prod-
uct. A special thank you goes to my lovely wife Ileana. She not only endured
all my time away from home (for the 3 months preceding and following my
son’s birth) working on this book, but she also provided serious technical
assistance, since she is a first-rate developer in her own right.
Both authors would like to thank their colleagues Mark Hernandez, Marc
Bacchus, John Rydzy, and Stephen Germany for their help in reviewing the
code samples and text for accuracy.
The authors would also like to thank the Wiley team of Rebecca Huehls,
Virginia Sanders, Tiffany Ma, and Terri Varveris for their help with this project.
01_599577 ffirs.qxp 5/1/06 12:08 PM Page v
Publisher’s Acknowledgments
We’re proud of this book; please send us your comments through our online registration form
located at

Gerry Fahey, Vice President of Production Services
Debbie Stailey, Director of Composition Services
01_599577 ffirs.qxp 5/1/06 12:08 PM Page vi
Contents at a Glance
Introduction .................................................................1
Part I: Basic PL/SQL Concepts.......................................7
Chapter 1: PL/SQL and Your Database ............................................................................9
Chapter 2: The PL/SQL Environment.............................................................................23
Part II: Getting Started with PL/SQL............................39
Chapter 3: Laying the Groundwork: PL/SQL Fundamentals .......................................41
Chapter 4: Controlling Program Flow ............................................................................85
Chapter 5: Handling Exceptions ...................................................................................105
Chapter 6: PL/SQL and SQL Working Together...........................................................127
Part III: Standards and Structures .............................163
Chapter 7: Putting Your Code in the Right Place........................................................165
Chapter 8: Creating Naming Standards .......................................................................187
Chapter 9: Creating Coding Standards ........................................................................201
Part IV: PL/SQL Data Manipulations ..........................219
Chapter 10: Basic Datatypes .........................................................................................221
Chapter 11: Advanced Datatypes .................................................................................253
Part V: Taking PL/SQL to the Next Level.....................289
Chapter 12: Transaction Control..................................................................................291
Chapter 13: Dynamic SQL and PL/SQL ........................................................................313
Chapter 14: PL/SQL Best Practices .............................................................................335
Part VI: The Part of Tens ...........................................355
Chapter 15: Ten PL/SQL Tips ........................................................................................357
Chapter 16: Ten Common Mistakes to Avoid in PL/SQL............................................377
Index .......................................................................397
02_599577 ftoc.qxp 5/1/06 12:09 PM Page vii
02_599577 ftoc.qxp 5/1/06 12:09 PM Page viii

Setting Up an Oracle Environment...............................................................23
Hardware and Software Requirements........................................................25
Accessing the Oracle Technology Network ................................................26
Installing the Database ..................................................................................27
Working with Code.........................................................................................28
SQL*Plus................................................................................................28
Oracle SQL Developer..........................................................................30
Third-party tools ..................................................................................30
02_599577 ftoc.qxp 5/1/06 12:09 PM Page ix