Oracle PL/SQL: Expert Techniques For Developers and Database Administrators

345 Pages·2009·3.11 MB·English

by  Lakshman Bulusu

O ® PL/SQL:
RACLE
E T
XPERT ECHNIQUES FOR
D D
EVELOPERS AND ATABASE
A
DMINISTRATORS
L B
AKSHMAN ULUSU
Australia, Brazil, Japan, Korea, Mexico, Singapore, Spain, United Kingdom, United States
Oracle®PL/SQL: Expert Techniques for © 2008 Course Technology, a part of Cengage Learning.
Developers and Database Administrators
ALL RIGHTS RESERVED. No part of this work covered by the copyright
Lakshman Bulusu herein may be reproduced, transmitted, stored, or used in any form or by
any means graphic, electronic, or mechanical, including but not limited to
photocopying, recording, scanning, digitizing, taping, Web distribution,
Publisher and General Manager, information networks, or information storage and retrieval systems, except
Course Technology PTR: as permitted under Section 107 or 108 of the 1976 United States Copyright
Stacy L. Hiquet Act, without the prior written permission of the publisher.
Associate Director of Marketing:
For product information and technology assistance, contact us at
Sarah Panella
Cengage Learning Customer & Sales Support, 1-800-354-9706
Manager of Editorial Services: For permission to use material from this text or product,
Heather Talbot submit all requests online atcengage.com/permissions
Further permissions questions can be emailed to
[e-mail]
Marketing Manager: Mark Hughes
Acquisitions Editor: Mitzi Koontz
Library of Congress Control Number: 2007939371
Project/Copy Editor: Kezia Endsley ISBN-13: 978-1-58450-554-9
ISBN-10: 1-58450-554-0
Technical Reviewer: Aneesha Bakharia
eISBN-10: 1-58450-612-1
CRM Editorial Services Coordinator:
Course Technology
Jen Blaney
25 Thomson Place
Boston, MA 02210
Interior Layout: Jill Flores
USA
Cengage Learning is a leading provider of customized learning solutions with
Cover Designer: Tyler Creative Services
office locations around the globe, including Singapore, the United Kingdom,
Australia, Mexico, Brazil, and Japan. Locate your local office at: international.
CD-ROM Producer: Brandon Penticuff
cengage.com/region
Indexer: Valerie Haynes Perry Cengage Learning products are represented in Canada by Nelson Education, Ltd.
Proofreader: Laura R. Gabler For your lifelong learning solutions, visit courseptr.com
Visit our corporate website at cengage.com
Printed in the United States of America
1 2 3 4 5 6 7 11 10 09 08
To the loving memory of my father-in-law Mr. G.V.S.J. Somayajulu,
who will always remain as a shining light to me.
Acknowledgments
I thank my wife, Anuradha, for her patience and my lovely kids Pranati and Pranav
for not giving me a hard time during the course of my entire writing period.
I thank my brother, Visweswar Kasi Bulusu, for his suggestions on various pro-
gramming language concepts that really proved invaluable.
I thank the publisher; the Acquisitions Editor, Mitzi Koontz; and the various
editors at CRM and Cengage Learning for their help in making this book see the
light of day.
About the Author
Lakshman Bulusu is an experienced Oracle professional with 15 plus years of ex-
tensive experience in software management, design, and development using Oracle
and its related technologies, including PL/SQL and Java. He’s the author of Oracle
Developer Forms Techniques(published by SAMS) and Oracle and Java Development,
(published by SAMS and translated into Polish and Japanese), Oracle9i PL/SQL: A
Developer’s Guide(published by Apress), and Oracle Programming FAQ(published
by NetImpress), all of which have been popular in the market. He also holds an
Oracle Masters credential and an OCP certification from Oracle Corporation, and is
a double honors graduate in computer science and engineering and mathematics.
He has presented at numerous international and national conferences on Oracle and
its tools and published articles in various technical magazines and journals in
the United States and the United Kingdom. Currently, as a Technical Manager for
Software Development, employed by Delaware-US-based Genex Technologies, he
oversees the management, design, and development of mission-critical Oracle
projects for major clients across the U.S. Europe, and Asia.
v
Contents
Introduction xiii
Part I Introductory Concepts 1
1 Introduction to PL/SQL 3
Why Use PL/SQL? 4
When Not to Use PL/SQL 5
PL/SQL Overview 5
Data Structure Management 6
Error Management 6
Data Management 6
Application Management 6
Transaction Management 7
Using PL/SQL in the Real World 7
Summary 11
2 PL/SQL by Dissection 13
Program Structure 14
Internal Architecture 18
PL/SQL Compilation 19
PL/SQL Execution 21
Memory Usage 22
Meta Data for PL/SQL Code 24
Program Debugging 25
Protecting Source Code 26
WRAP Utility 26
DBMS_DDL Package 26
Summary 28
Contents vii
3 New Features in PL/SQL 29
Introduction 29
New Features in Oracle 11g 30
New PLW-06009 Warning When a WHEN OTHERS Clause
Does Not Have a RAISE or RAISE_APPLICATION_ERROR 30
Fine-Grained Dependency Tracking 31
Dynamic SQL Enhancements 31
PL/SQL Triggers Enhancements 31
PL/SQL Intra-Unit Inlining (Efficiency) 32
SQL and PL/SQL Result Caching (Efficiency) 33
Enhanced Native Compilation 34
Other Miscellaneous Enhancements 34
New Features in Oracle 10g 35
An Enhanced DBMS_OUTPUT Package (Oracle 10gR2) 35
Tracking the Error Line Number 35
Compile-Time Warnings 38
Programmer-Defined Quote Delimiter for String Literals 39
Enhanced FORALL Statement for Handling DML
Array Processing with Sparse Collections 40
Conditional Compilation (Oracle 10gR2) 45
Nested Table Enhancements 47
Regular Expressions 47
Enhanced Email Facility from PL/SQL 48
New PL/SQL API for Linear Matrix Math (Oracle 10gR2) 49
Summary 49
viii Contents
Part II Techniques for PL/SQL Programming 51
4 Data Structure Management in PL/SQL 53
Techniques for Using Records 54
Data I/O Using Records 54
Using Functions to Perform Record Comparison 56
Techniques for Using Associative Arrays 60
Performing Data I/O 61
Caching Lookup Data 67
Techniques for Using Nested Tables 72
Passing Resultsets Between PL/SQL Programs 72
Manipulating Stored Nested Tables Using SQL 77
Simulating Multi-Dimensional Arrays 83
Records versus Objects 89
Summary 90
5 Error Management in PL/SQL 91
Techniques for Obtaining Complete Error Information 93
Tracking the Error Line Number 98
Techniques for Customizing the Error Info Returned 100
Techniques for Halting Further Processing on Error Occurrences 101
Throwing, Trapping, and Handling Customized Errors 104
Summary 108
6 Data Management in PL/SQL 109
Data-Retrieval Techniques 110
Data-Manipulation Techniques 112
Array Processing in PL/SQL 113
Array Processing Using Static SQL 114
Techniques for Bulk DML 117
Bulk DML with Sparse Collections 132
Contents ix
Techniques for Bulk Querying 137
Array Processing Using Dynamic SQL 141
Summary 142
7 Application Management in PL/SQL 143
Procedures versus Functions 144
Calling from SQL 144
Creating a Function-Based Index 145
Creating a Table Function 146
Using User-Defined Operators and Functions 146
Coding Packages to Globalize Data and Code 147
Packages versus Contexts and Globalizing Data 149
Coding Packages to Localize Data and Privatize Code 150
Coding Packages to Break the Dependency Chain 150
Reading and Writing Package Variables Dynamically 157
Coding Packages to Pass Data Between Applications 160
Using Cursor Variables 161
Using Collections 161
Packages versus Objects as Application Structures 164
Supplementing Data Integrity Using Triggers 165
Summary 167
8 Transaction Management in PL/SQL 169
Asynchronous COMMIT 170
Autonomous Transaction Techniques 171
Using Autonomous Transactions for Error Logging 172
Using COMMIT Inside a Database Trigger 176
Techniques for Auditing Queries 177
Summary 192