Oracle

Mastering Oracle PL/SQL: Practical Solutions
CONNOR MCDONALD, WITH CHAIM KATZ, CHRISTOPHER BECK, JOEL R. KALLMAN, AND DAVID C. KNOX

Mastering Oracle PL/SQL: Practical Solutions Copyright © 2004 by Connor McDonald, with Chaim Katz, Christopher Beck, Joel R. Kallman, and David C. Knox
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN (pbk): 1-59059-217-4 Printed and bound in the United States of America 12345678910 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, weuse the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Technical Reviewers: Jakob Hammer-Jakobsen, Torben Holm, Thomas Kyte, Connor McDonald Technical Editor: Tony Davis Editorial Board: Steve Anglin, Dan Appleman, Gary Cornell, James Cox, Tony Davis, John Franklin, Chris Mills, Steven Rycroft, Dominic Shakeshaft,Julian Skinner, Martin Streicher, Jim Sumser, Karen Watterson, Gavin Wray, John Zukowski Assistant Publisher: Grace Wong Project Manager: Tracy Brown Collins Copy Editors: Nancy Depper, Nicole LeClerc Production Manager: Kari Brooks Production Editor: Janet Vail Proofreader: Patrick Vincent Compositor: Gina M. Rexrode, Point n’ Click Publishing, LLC Indexer: Valerie Perry Artist: ChristineCalderwood, Kinetic Publishing Services, LLC Cover Designer: Kurt Krames Manufacturing Manager: Tom Debolski Distributed to the book trade in the United States by Springer-Verlag New York, Inc., 175 Fifth Avenue, New York, NY, 10010 and outside the United States by Springer-Verlag GmbH & Co. KG, Tiergartenstr. 17, 69112 Heidelberg, Germany. In the United States: phone 1-800-SPRINGER, emailorders@springer-ny.com, or visit http://www.springer-ny.com. Outside the United States: fax +49 6221 345229, email orders@springer.de, or visit http://www.springer.de. For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, email info@apress.com, or visit http://www.apress.com. The information in this book isdistributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers athttp://www.apress.com in the Downloads section. You will need to answer questions pertaining to this book in order to successfully download the code.

Contents at a Glance
Foreword to the OakTable Press Series . . . . . . . . . . . . . . . . . . . . . . . . . . . .ix About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi About theTechnical Reviewers Introduction Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10 Appendix A
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiii

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv
. . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . . . . . . . . . .xv

Setting Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix Efficient PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1 Package It All Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59 The Vexed Subject of...