Mastering Oracle PL/SQL: Practical Solutions

2174fmfinal.qxd

11/24/03

4:02 PM

Page i

Mastering Oracle

PL/SQL: Practical

Solutions

CONNOR MCDONALD, WITH CHAIM KATZ,

CHRISTOPHER BECK, JOEL R. KALLMAN, AND DAVID C. KNOX

2174fmfinal.qxd

11/24/03

4:02 PM

Page ii

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, we use 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: Christine Calderwood, 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, email orders@springer-, or visit

. Outside the United States: fax +49 6221 345229, email

orders@springer.de, or visit .

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@, or visit

.

The information in this book is distributed 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 at in the

Downloads section. You will need to answer questions pertaining to this book in order to successfully download the code.

2174fmfinal.qxd

11/24/03

4:02 PM

Page iii

Contents at a Glance

Foreword to the OakTable Press Series . . . . . . . . . . . . . . . . . . . . . . . . . . . .ix

About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi

About the Technical Reviewers

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiii

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv

Introduction

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xv

Setting Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix

Chapter 1

Efficient PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1

Chapter 2

Package It All Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59

Chapter 3

The Vexed Subject of Cursors . . . . . . . . . . . . . . . . . . . . .117

Chapter 4

Effective Data Handling . . . . . . . . . . . . . . . . . . . . . . . . . . .145

Chapter 5

PL/SQL Optimization Techniques . . . . . . . . . . . . . . . . . . .229

Chapter 6

Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .307

Chapter 7

DBA Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .367

Chapter 8

Security Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .415

Chapter 9

Web Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .463

Chapter 10

PL/SQL Debugging

Appendix A

Building DEBUG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .559

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .505

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .587

2174fmfinal.qxd

11/24/03

4:02 PM

Page iv

2174fmfinal.qxd

11/24/03

4:02 PM

Page v

Contents

Foreword to the OakTable Press Series

About the Authors

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi

About the Technical Reviewers

Acknowledgments

Introduction

Setting Up

. . . . . . . . . . . . . . . . . . . .ix

. . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiii

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xv

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix

Chapter 1 Efficient PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

Why Use PL/SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1

What is Efficient PL/SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4

Achieving Efficiency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13

Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56

Chapter 2 Package It All Up

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

Basic Benefits of Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59

Standalone Procedures and the Dependency Crisis . . . . . . . . . . . . . . . .65

Breaking the Dependency Chain . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .75

Enabling Recursion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90

Why Have People Avoided Packages? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .91

When Not to Use Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .96

Delivered Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115

v

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download