Mastering Oracle Pl/SQL

by McDonald, Connor; Katz, Chaim; Beck, Christopher; Kallman, Joel R.; Knox, David C.; Hubeny, Frank

• ISBN13: 9781590592175
• ISBN10: 1590592174
• Format: Paperback
• Copyright: 2003-12-01
• Publisher: Apress

Summary

This book is about cool, effective techniques to solve essential problems using PL/SQL. It focuses in on the fundamental programming unit, the stored procedure, and demonstrates how to code them in the most effective manner and on real practical uses for them in the database. In short: if you have mastered the fundamentals of the PL/SQL language, and are now looking for an in- depth, practical guide to solving real problems with PL/SQL stored procedures, then this is the book for you.

Table of Contents

Foreword to the OakTable Press Series	ix
About the Authors	xi
About the Technical Reviewers	xiii
Acknowledgments	xiv
Introduction	xv
Setting Up	xix
Efficient PL/SQL	1	(58)
Why Use PL/SQL?	1	(3)
What is Efficient PL/SQL?	4	(9)
Achieving Efficiency	13	(43)
Conclusion	56	(3)
Package It All Up	59	(58)
Basic Benefits of Packages	59	(6)
Standalone Procedures and the Dependency Crisis	65	(10)
Braking the Dependency Chain	75	(15)
Enabling Recursion	90	(1)
Why Have People Avoided Packages?	91	(5)
When Not to Use Package	96	(3)
Delivered Pacakages	99	(16)
Summary	115	(2)
The Vexed Subject of Cursors	117	(28)
Implicit vs. Explicit Cursors	117	(15)
Cursor Management Across Architectures	132	(11)
Summary	143	(2)
Effective Data Handling	145	(84)
Taking Control of Your Datatypes	145	(10)
From Fields to Rows-Using %Rowtype	155	(7)
From Records to Objects	162	(14)
The Motivation for Collection in PL/SQL	176	(4)
Bulking Up with Collections	180	(17)
Passing Variables Between PL/SQL Programs	197	(15)
Transaction Processing in PL/SQL	212	(6)
Autonomous Transactions	218	(10)
Summary	228	(1)
PL/SQL Optimization Techniques	229	(78)
Minimizing Parsing and Memory Consumption	229	(25)
Data Types: Tips and Techniques	254	(13)
Calling PL/SQL	267	(20)
SQL Within PL/SQL	287	(19)
Summary	306	(1)
Triggers	307	(60)
Trigger Concepts	307	(12)
DML Triggers	319	(5)
Instead-of Triggers	324	(2)
Mutating Tables	326	(10)
Data Auditing	336	(3)
Table Varsioning	339	(3)
Oracle Streams	342	(7)
Job Queue (Temporal Even Triggers)	349	(6)
DDL Triggers	355	(4)
Database Event Triggers	359	(1)
Logon Triggers	359	(4)
Don't Re-Invent the Wheel	363	(3)
Summary	366	(1)
DBA Packages	367	(48)
Alert File Package	368	(26)
Notification Package	394	(5)
Proactive Monitoring Package	399	(7)
Historical Data Package	406	(7)
Summary	413	(2)
Security Packages	415	(48)
Design Considerations	415	(25)
Triggers for Security	440	(13)
Protecting the Source	453	(9)
Summary	462	(1)
Web Packages	463	(42)
PL/SQL Web Toolkit Basic	463	(5)
The htp and htf Packages	468	(2)
Using Environment Variables	470	(6)
Cookies	476	(2)
Managing Files	478	(6)
Managing Tables Through the Web	484	(11)
HTTP from the Database	495	(8)
Summary	503	(2)
PL/SQL Debugging	505	(54)
Defensive Coding	505	(7)
Tools	512	(28)
A Custom Debug Utility	540	(18)
Summary	558	(1)
Appendix A Building Debug	559	(28)
Database Design and Setup	559	(3)
Package Layout	562	(2)
Implementation	564	(22)
Troubleshooting Debug	586	(1)
Index	587