After publishing more than 1,600 pages on Oracle PL /SQL in two previous books, I marvel at the existence now of this third book covering yet other aspects of the PL /SQL language. I can still remember quite distinctly a moment in September, 1994, when I embarked on writing the first draft of Oracle PL /SQL Programming and wondered: are there really 400 pages worth of material on that much-used and often-maligned procedural language from a nonprocedural (SQL) company? If the answer to that question was a resounding "yes" in 1994, then the answer is a deafening roar today!
Maybe PL /SQL isn't the answer to every object-oriented programmer's deepest desires. Maybe developers are badly in need of -- and unreservedly deserve -- better tools with which to write, debug, and reuse PL /SQL programs. Maybe PL /SQL isn't perfect, but the reality is that hundreds of thousands of people around the world work (and struggle) with PL /SQL on a daily basis. We all need as much information as possible about how we can make the best possible use of Oracle PL /SQL.
And that is the objective of Oracle Built-in Packages. If you are going to build complex applications using PL /SQL, you will not succeed unless you learn about and figure out how to utilize many of the packages described in this book. Packages are the method of choice for Oracle and third parties like RevealNet, Inc., to extend the base PL /SQL language, to improve ease of use, and to provide brand-new functionality in the language. Writing PL /SQL code without knowing about or using built-in packages is akin to building an automobile and ignoring the last 20 years of technological advances. The resulting machine will run more slowly, use more gas, and be harder to repair.
Oracle Built-in Packages grew out of Chapter 15 of the first edition of Oracle PL /SQL Programming. When Oracle released Oracle8, it was time to update that book to include the wide-ranging new PL /SQL8 functionality. It was clear from the start that this second edition, if organized like the first, would have been well over 1,500 pages in length -- a totally impractical size for a developer's handbook.
What to do? Based on feedback from developers about Oracle PL /SQL Programming, there was an enormous amount of interest in, and often confusion surrounding, the built-in packages. These Oracle-provided "add-ons" to PL /SQL clearly needed more detailed coverage, more examples, more tips, more of just about everything. My single chapter of 100 pages was woefully inadequate. We made the decision to move that single chapter out of Oracle PL /SQL Programming and expand it into a book all its own. You are holding the result.
I recognized early in the process that I couldn't personally cover all of the Oracle built-in packages discussed in this book. I didn't have the necessary expertise, nor the time to learn, nor the time to write it all. So I sought and received the help of two excellent Oracle technologists: John Beresniewicz and Charles Dye.
Over the past six months, John, Charles, and I have researched the packages provided by Oracle in the database, verified the documentation, uncovered aberrant behavior, and discovered neat tricks. We also made it a priority to construct package-based utilities that you will be able to put to immediate use.
While Oracle Built-in Packages is a collaborative effort, it is also a combination of very individual efforts. As such, you will find differences in coding styles and philosophies. Rather than try to enforce a single standard throughout, I welcomed the variations (as long as all contributed in their own way to a deeper, clearer understanding of the PL /SQL technology). There is rarely a single right way to do anything, and there is an enormous amount we can learn from the different journeys each of us takes to a solution.
For purposes of directing feedback and questions, you may find it useful to know who wrote each of the chapters. In the next section, "Structure of this Book," you will find the names of the authors listed with their chapters. You will also see that there are two other names: Chuck Sisk and Chip Dawes. While many people helped in many ways to produce Oracle Built-in Packages (see the "Acknowledgements" for details), Chuck and Chip actually contributed entire chapters.
This book is divided into four parts:
Chapter 1, Introduction, introduces you to PL /SQL packages generally and built-in packages specifically. It shows you how to call packaged code from your own programs and includes tips for handling the exceptions raised when you call a packaged program. (Steven)
Chapter 2, Executing Dynamic SQL and PL/SQL, shows you how to use the DBMS_SQL package to construct and execute SQL statements and PL /SQL blocks at run-time. (Steven)
Chapter 3, Intersession Communication, shows you how to use DBMS_PIPE and DBMS_ALERT to communicate information between different Oracle sessions. You can also use DBMS_PIPE to communicate with processes external to Oracle. (John)
Chapter 4, User Lock and Transaction Management, introduces DBMS_LOCK, a handy but rarely used package that provides an interface to the Oracle lock manager, and DBMS_TRANSACTION, which offers several programs that affect transaction behavior in your PL /SQL program. (DBMS_LOCK -- John; DBMS_TRANSACTION -- Steven)
Chapter 5, Oracle Advanced Queuing, contains an extensive treatment of Oracle Advanced Queuing, a powerful queuing mechanism available with Oracle8. You'll use this mechanism through the DBMS_AQ and DBMS_AQADM packages. (Steven)
Chapter 6, Generating Output from PL/SQL Programs, shows you how to send information from your program either to the screen , using DBMS_OUTPUT, or to a server-side file, using UTL_FILE. (Steven)
Chapter 7, Defining an Application Profile, familiarizes you with a handy package, DBMS_APPLICATION_INFO. You'll use it to "register" the current execution status of your application with the Oracle database. (John)
Chapter 8, Managing Large Objects , shows you how Oracle8 provides robust support for large objects (sometimes known as "BLOBs" or "LOBs"), and how the DBMS_LOB built-in package allows you to access and manipulate these LOBs from within a PL /SQL program. (Chuck)
Chapter 9, Datatype Packages, collects together several packages that specialize in manipulating different types of data. DBMS_ROWID makes it easy to work with the two different ROWID formats available in Oracle8. UTL_RAW allows you to work with raw data. UTL_REF, new in Oracle8 Release 8.1, provides a PL /SQL interface to select and modify objects (instances of an object type) in an object table without having to specify or know about the underlying database table. (DBMS_ROWID -- Steven; UTL_RAW -- Chip; UTL_REF -- Steven)
Chapter 10, Miscellaneous Packages, contains coverage of a number of packages: DBMS_UTILITY (the actual "miscellaneous" package), DBMS_DDL (contains programs to recompile stored code, analyze objects in your schema, and modify how object identifiers may be referenced in Oracle8), DBMS_RANDOM (a random number generator), and DBMS_DESCRIBE (use it to get information about the parameters of a stored program). (Steven)
Chapter 11, Managing Session Information, introduces you to DBMS_SESSION and DBMS_SYSTEM, two packages that help you analyze and manage information about your current session. (John)
Chapter 12, Managing Server Resources, presents DBMS_SPACE and DBMS_SHARED_POOL, which contain handy tools for database administrators to help them manage database-related resources on the server. (John)
Chapter 13, Job Scheduling in the Database, shows you how to use DBMS_ JOB to schedule the execution of stored procedures without the use of operating system-specific schedulers, such as UNIX's cron. (John)
Chapter 14, Snapshots, explores the packages, DBMS_SNAPSHOT, DBMS_REFRESH, and DBMS_OFFLINE_SNAPSHOT, and some programs in DBMS_REPCAT, showing how to maintain snapshots, snapshot groups, and snapshot logs. (Charles)
Chapter 15, Advanced Replication, explains how to use DBMS_REPCAT, DBMS_REPUTIL, DBMS_OFFLINE_OG, DBMS_REPCAT_ADMIN, DBMS_REPCAT_AUTH, and DBMS_RECTIFIER_DIFF to create and administer your replicated databases. (Charles)
Chapter 16, Conflict Resolution, shows you how to configure Oracle to automatically detect, correct, and report many forseeable conflicts by using procedures in DBMS_REPCAT to create and maintain custom resolution methods. (Charles)
Chapter 17, Deferred Transactions and Remote Procedure Calls, introduces the DBMS_DEFER package and shows you how to queue deferred remote procedure calls (RPCs) and use DBMS_DEFER_QUERY and DBMS_DEFER_SYS to perform administrative and diagnostic activities. (Charles)
Appendix A, What's on the Companion Disk?, explains how to install and use the software on the companion diskette. (A group effort)
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.