WIXLIB

2174fmfinal.qxd11/24/034:02 PMPage iMastering OraclePL/SQL: PracticalSolutionsCONNOR MCDONALD, WITH CHAIM KATZ,CHRISTOPHER BECK, JOEL R. KALLMAN, AND DAVID C. KNOX

2174fmfinal.qxd11/24/034:02 PMPage iiMastering Oracle PL/SQL: Practical SolutionsCopyright 2004 by Connor McDonald, with Chaim Katz, Christopher Beck, JoelR. Kallman, and David C. KnoxAll rights reserved. No part of this work may be reproduced or transmitted in any form or by anymeans, electronic or mechanical, including photocopying, recording, or by any informationstorage or retrieval system, without the prior written permission of the copyright owner and thepublisher.ISBN (pbk): 1-59059-217-4Printed and bound in the United States of America 12345678910Trademarked names may appear in this book. Rather than use a trademark symbol with everyoccurrence of a trademarked name, we use the names only in an editorial fashion and to thebenefit of the trademark owner, with no intention of infringement of the trademark.Technical Reviewers: Jakob Hammer-Jakobsen, Torben Holm, Thomas Kyte, Connor McDonaldTechnical Editor: Tony DavisEditorial Board: Steve Anglin, Dan Appleman, Gary Cornell, James Cox, Tony Davis, JohnFranklin, Chris Mills, Steven Rycroft, Dominic Shakeshaft, Julian Skinner, Martin Streicher, JimSumser, Karen Watterson, Gavin Wray, John ZukowskiAssistant Publisher: Grace WongProject Manager: Tracy Brown CollinsCopy Editors: Nancy Depper, Nicole LeClercProduction Manager: Kari BrooksProduction Editor: Janet VailProofreader: Patrick VincentCompositor: Gina M. Rexrode, Point n’ Click Publishing, LLCIndexer: Valerie PerryArtist: Christine Calderwood, Kinetic Publishing Services, LLCCover Designer: Kurt KramesManufacturing Manager: Tom DebolskiDistributed to the book trade in the United States by Springer-Verlag New York, Inc., 175 FifthAvenue, 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, email orders@springer-ny.com, or visithttp://www.springer-ny.com. Outside the United States: fax 49 6221 345229, emailorders@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 visithttp://www.apress.com.The information in this book is distributed on an “as is” basis, without warranty. Although everyprecaution has been taken in the preparation of this work, neither the author(s) nor Apress shallhave any liability to any person or entity with respect to any loss or damage caused or alleged tobe caused directly or indirectly by the information contained in this work.The source code for this book is available to readers at http://www.apress.com in theDownloads section. You will need to answer questions pertaining to this book in order to successfully download the code.

2174fmfinal.qxd11/24/034:02 PMPage iiiContents at a GlanceForeword to the OakTable Press Series . . . . . . . . . . . . . . . . . . . . . . . . . . . .ixAbout the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiAbout the Technical Reviewers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiiiAcknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xivIntroduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xvSetting Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xixChapter 1Efficient PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1Chapter 2Package It All Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59Chapter 3The Vexed Subject of Cursors . . . . . . . . . . . . . . . . . . . . .117Chapter 4Effective Data Handling . . . . . . . . . . . . . . . . . . . . . . . . . . .145Chapter 5PL/SQL Optimization Techniques . . . . . . . . . . . . . . . . . . .229Chapter 6Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .307Chapter 7DBA Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .367Chapter 8Security Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .415Chapter 9Web Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .463Chapter 10PL/SQL DebuggingAppendix ABuilding DEBUG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .559. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .505Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .587

2174fmfinal.qxd11/24/034:02 PMPage iv

2174fmfinal.qxd11/24/034:02 PMPage vContentsForeword to the OakTable Press SeriesAbout the Authors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiAbout the Technical ReviewersAcknowledgmentsIntroductionSetting Up. . . . . . . . . . . . . . . . . . . .ix. . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiii. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xv. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xixChapter 1 Efficient PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1Why Use PL/SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1What is Efficient PL/SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4Achieving Efficiency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56Chapter 2 Package It All Up. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59Basic Benefits of Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59Standalone Procedures and the Dependency Crisis . . . . . . . . . . . . . . . .65Breaking the Dependency Chain . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .75Enabling Recursion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90Why Have People Avoided Packages? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .91When Not to Use Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .96Delivered Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115v

2174fmfinal.qxd11/24/034:02 PMPage viChapter 3 The Vexed Subject of Cursors. . . . . . . . . . . . . . . 117Implicit vs. Explicit Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117Cursor Management Across Architectures . . . . . . . . . . . . . . . . . . . . . . . . . .132Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .143Chapter 4 Effective Data Handling . . . . . . . . . . . . . . . . . . . . . . . 145Taking Control of Your Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .145From Fields to Rows—Using %ROWTYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .155From Records to Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .162The Motivation for Collections in PL/SQL . . . . . . . . . . . . . . . . . . . . . . .176Bulking Up with Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .180Passing Variables Between PL/SQL Programs . . . . . . . . . . . . . . . . . . . . . .197Transaction Processing in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .212Autonomous Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .218Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .228Chapter 5 PL/SQL Optimization Techniques. . . . . . . . . . . . 229Minimizing Parsing and Memory Consumption . . . . . . . . . . . . . . . . . . . . . .229Data Types: Tips and Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .254Calling PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .267SQL Within PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .287Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .306Chapter 6 Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307Trigger Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .307DML Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .319Instead-of Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .324Mutating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .326Data Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .336Table Versioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .339Oracle Streams . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .342Job Queue (Temporal Event Triggers) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .349DDL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .355Database Event Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .359Logon Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .359Don’t Re-Invent the Wheel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .363Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .366vi