About the Second Edition

We (the authors and O'Reilly & Associates) are committed to providing comprehensive, useful coverage of the PL/SQL language over the life of this language. The first edition of this book covered most of PL/SQL's features as it existed through PL/SQL Release 2.3. With the release of Oracle8, however, we faced a challenge: how do we fit all the new technologies of PL/SQL8 into Oracle PL/SQL Programming and fill out coverage of existing elements of PL/SQL without creating a tome so unwieldy that reading the book becomes as much a physical as a mental workout?

Furthermore, if we look ahead a few years, we can easily expect that Oracle will continue to roll out expanded functionality in the objects area, as well as providing new and enhanced built-in packages. Given this situation, it quickly became clear to us that it was not practical to offer a single text which covered "all things PL/SQL."

Two questions then arose: what do we cut and what do we do with the stuff that we cut? The answers are as follows:

• Move the package examples from the printed text to the disk. The second edition's disk offers a Windows-based interface designed by RevealNet, Inc. (http://www.revealnet.com) allowing you rapid access to the many utilities and samples. Now, instead of having to look through a directory listing and then using an editor to view those files, you will be able to mouse-click and drill-down your way to the topics in which you are interested. We take advantage of this interface to give you an easy way to examine the many examples of packages I built for the book.

• Remove the chapter on built-in packages and expand it into a complete book. This chapter in the first edition offered lots of good information, but it was not comprehensive and simply couldn't keep growing to absorb all the new technology issuing forth from Oracle headquarters. Moving coverage of built-in packages to its own book will give us room to provide more and better information about this key element of the PL/SQL language. Appendix A, What's on the Companion Disk?, of this edition of Oracle PL/SQL Programming will still provide a quick reference to the more common built-in packages.

About the Contents

The second edition of Oracle PL/SQL Programming is divided into seven parts:

Part 1, Programming in PL/SQL

Chapters 1 through 3 explain what it means to program in PL/SQL and in Oracle-based applications in general. This part of the book introduces you to the main features of the PL/SQL language and gives you some general advice on programming habits and effective coding style.

Part 2, PL/SQL Language Elements

Chapters 4 through 10 describe the basic PL/SQL programming components -- variables, cursors, conditional and sequential control statements, loops, exception handlers, PL/SQL records, and PL/SQL tables. These chapters contain numerous examples, along with tips on how, when, and where to apply these programming constructs most effectively. When you complete this section of the book you will know how to apply all the constructs of the PL/SQL language. You will also be able to examine the complex requirements in your own applications and use the different parts of PL/SQL to implement those requirements.

Part 3, Built-In Functions

Chapters 11 through 14 present the many built-in (predefined) PL/SQL functions and procedures, which you can put to use immediately in your applications. One of the key barometers of your success with PL/SQL will be the extent to which you know how to leverage all of the capabilities that Oracle Corporation provides. (You don't want to have to reinvent the wheel when so many functions and procedures are provided for you.) Appendix C, Built-In Packages supplements Part 3 by summarizing the syntax of Oracle's built-in packages.

Part 4, Modular Code

Chapters 15 through 17 take you past the individual components of the PL/SQL language to explore modular construction in PL/SQL. You will learn how to build procedures, functions, and packages; more than that, you will learn how to build them correctly. More than with any other aspect of code, the way you design your modules has an enormous impact on your applications' development time, reusability, and maintainability.

Part 5, New PL/SQL8 Features

Chapters 18 through 21 contain the main discussion of the new features of Oracle8 -- in particular, object types, new types of collections (nested tables and VARRAYs), object views, and external procedures. Although we describe Oracle8 enhancements as appropriate in other sections of the book, we localize the major discussion here. Doing so allows developers new to Oracle8 to learn about the features in a coordinated way. It also lets developers still working on Oracle7 systems to skip over much of the discussion until it is more relevant to them.

Part 6, Making PL/SQL Programs Work

Chapters 22 through 26 tell you how to manage your PL/SQL code and debug, tune, and trace the execution of your programs. This part of the book also contains a summary of particularly helpful tips for effective PL/SQL programming in the real world.

Part 7, Appendixes

Appendixes A through C summarize what's on the companion disk, how to call stored procedures from PL/SQL Version 1.1, and how to call Oracle's built-in packages.

If you are new to PL/SQL, reading this book from beginning to end should improve your skills and help your understanding widen in a gradual, natural process. If you're already a proficient PL/SQL programmer, you'll probably want to dip into the appropriate sections in order to extract particular techniques for immediate application. Whether you use this book as a teaching guide or as a reference, I hope that it will have a significant impact on your ability to use PL/SQL most effectively.

Long as this book is, it doesn't do everything. The Oracle environment is a huge and complex one, and in this book I've focused my attention on PL/SQL itself. Some topics are outside the logical scope of this book. Therefore, this book does not cover:

• Administration of Oracle databases. While the DBA can use this book to learn how to write the PL/SQL needed to build and maintain databases, this book does not explore all the nuances of the Data Definition Language (DDL) of Oracle's SQL.

• Application and database tuning. I don't cover detailed tuning issues in this book, though the second edition does add a chapter on PL/SQL tuning. Oracle Performance Tuning by Peter Corrigan and Mark Gurry (O'Reilly & Associates, Second Edition, 1997) gives you all the information you need about tuning your Oracle applications.

• Oracle tool-specific technologies independent of PL/SQL. This book won't teach you everything you need to know about such technologies as SQL*Forms and Oracle Forms triggers. Similarly, you won't find detailed discussions of repeating frames of Oracle Reports in here. Nevertheless, many of the techniques offered in this book certainly do apply to the Oracle Developer/2000 environment.

• Third-party application development software. There are many alternatives to using PL/SQL and the tools supplied by Oracle to build your applications. This book does not address these options, nor does it attempt to compare PL/SQL to these third-party products.

• National Language Support in Oracle. This book does not offer comprehensive coverage of Oracle's National Language Support (NLS) capabilities for developing applications for multiple languages.

• Trusted Oracle. Oracle Corporation has developed a special version of its Oracle7 Server for high-security environments. This book does not detail the additional datatypes and features available only for Trusted Oracle.