Objectives of This Book

What, specifically, will this book help you do?

Take full advantage of PL/SQL

Oracle's reference manuals may describe all the features of the PL/SQL language, but they don't tell you how to apply the technology. In fact, in some cases, you'll be lucky to even understand how to use a given feature after you've made your way through the railroad diagrams. Books and training courses tend to cover the same standard topics in the same limited way. In this book, we'll venture beyond the basics to the far reaches of the language, finding the nonstandard ways that a particular feature can be tweaked to achieve a desired result.

Use PL/SQL to solve your problems

You don't spend your days and nights writing PL/SQL modules so that you can rise to a higher plane of existence. You use PL/ SQL to solve problems for your company or your customers. In this book, we try hard to help you tackle real-world problems, the kinds of issues developers face on a daily basis (at least those problems that can be solved with mere software). To do this, we've packed the book with examples—not just small code fragments, but substantial application components that you can apply immediately to your own situations. There is a good deal of code in the book itself, and much more on the accompanying web site. In a number of cases, we use the code examples to guide you through the analytical process needed to come up with a solution. In this way you'll see, in the most concrete terms, how to apply PL/SQL features and undocumented applications of those features to a particular situation.

Write efficient, maintainable code

PL/SQL and the rest of the Oracle products offer the potential for incredible development productivity. If you aren't careful, however, this capability will simply let you dig yourself into a deeper, darker hole than you've ever found yourself in before. We would consider this book a failure if it only helped programmers write more code in less time; we want to help you develop the skills and techniques to build applications that readily adapt to change and that are easily understood and maintained. We want to teach you to use comprehensive strategies and code architectures that allow you to apply PL/SQL in powerful, general ways to the problems you face.

Structure of This Book

Both the authors and O'Reilly & Associates are committed to providing comprehensive, useful coverage of PL/SQL over the life of the language. The first edition of this book covered most of PL/SQL's features as they existed through PL/SQL Release 2.3. The second edition added coverage of Oracle8's new PL/SQL features, but separated that content from the rest of the book. The third edition, which you are now holding, takes a different approach. It includes all PL/SQL language features up to and including Oracle9i Release 2 (9.2). So regardless of the version you are using, from Oracle 7.3.4 (although we hope you won't be using that one much longer) to Oracle9i, this book will help you answer your questions. And this information is now completely integrated; there is no separate section for Oracle9i. Instead, you simply go to the section of the book with the feature you are interested in, and you will see what is available in the various versions.

When Steven first started writing about PL/SQL in 1994, it was actually possible to offer comprehensive coverage of the language in a single, albeit large, volume. By 1997, that was no longer the case, which is why there are many additional books from O'Reilly on such topics as the built-in or Oracle-supplied packages (e.g., DBMS_SQL); see Section 1.5.1 in Chapter 1 for information about these other books. Now, in 2002, the rapid developments in the PL/SQL language made us wonder: how can we provide a user guide and reference to the PL/SQL language without creating a tome so unwieldy that reading the book becomes as much a physical as a mental workout?

We took the following steps in shaping the book:

· First, and most importantly, we added crucial content that had been missing for years, most notably the treatment of database triggers.

· Next, we decided to leverage the Internet more fully. Rather than take up page after page with extended (and often hard to follow)code examples, we have shifted such topics to the book's Web Companion, available on the O'Reilly web site at http://www.oreilly.com/catalog/oraclep3 (see About the Code for details). Don't worry—the book is still absolutely packed full of code; we're just more concise than in the past.

· With much reluctance, we moved entire chapters from the second edition to the book's web site. These include the second edition's Chapter 17, Calling PL/SQL Functions in SQL, and Chapter 22, Code Design Tips. (Code design tips are also covered extensively in Oracle PL/SQL Best Practices).

· We reorganized a number of chapters, both to improve the flow of the book and to reduce the overall page count. For example, rather than treating character data separately from character functions, we now have a single chapter that covers everything relating to strings. We combined several chapters, most notably the old Chapters 24 through 26 (covering debugging, tuning, and tracing) into a single chapter. Large swaths of text, mostly anecdotes, that didn't make it into the third edition can be found on the web site.

· A new and more compact O'Reilly interior book design saved us a good 10% in overall page count over the previous format. The new format was also crucial in squeezing more code onto lines and pages.

We are very happy with the results, and hope that you will be too. There is more information than ever before, but we managed to present it without losing that "trademark" sense of humor and conversational tone that readers have told us for years make the book readable, understandable, and highly useful.

One comment regarding the "voice" behind the text. You may notice that in some parts of this book we use the word "we," and in others "I." One characteristic of this book (and one for which readers have expressed appreciation) is the personal voice that's inseparable from the text. Consequently, even with the addition of coauthors to the book (and, in the third edition, significant contributions from several other people), we've decided to maintain the use of "I" when an author speaks in his own voice.

Rather than leave you guessing as to which author is represented by the "I" in a given chapter, we thought we'd offer this quick guide for the curious: