Oracle PL/SQL Programming, Third Edition

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:

Chapter	Author		Chapter	Author
1	Steven		13	Steven
2	Bill		14	Steven
3	Bill and Steven		15	Steven
4	Jonathan and Steven		16	Steven
5	Steven		17	Steven
6	Steven		18	Darryl
7	Steven		19	Steven
8	Jonathan and Steven		20	Bill
9	Jonathan and Steven		21	Bill
10	Jonathan and Steven		22	Steven
11	Steven and Bill		23	Bill
12	Jonathan

About the Contents

The third edition of Oracle PL/SQL Programming is divided into six parts:

Part I, Programming in PL/SQL

We start from the very beginning in Chapter 1: where did PL/SQL come from? What is it good for? We offer a very quick review of some of the main features of the PL/SQL language. Chapter 2 is designed to get you and up and running PL/SQL programs as quickly as possible: it contains clear, straightforward instructions for executing PL/SQL code in SQL*Plus and a few other common environments. Chapter 3 reviews fundamentals of the PL/SQL language: what makes up a PL/SQL statement, an introduction to the block structure, how to write comments in PL/SQL, and so on.

Part II, PL/SQL Program Structure

Chapter 4 through Chapter 6 explore conditional (IF and CASE) and sequential (e.g., GOTO and NULL) control statements, loops, and exception handling in the PL/SQL language. This section of the book will teach you to construct blocks of code that correlate to the complex requirements of your applications.

Part III, PL/SQL Program Data

Just about every program you write will manipulate data, and much of that data will be local to (defined in) your PL/SQL procedure or function. Chapter 7 through Chapter 12 concentrate exhaustively on the various types of program data you can define in PL/SQL, such as numbers, strings, records, and collections. You will learn about the new datatypes introduced in Oracle9i, such as INTERVAL, TIMESTAMP, XMLType, and others. These chapters also cover the various built-in functions provided by Oracle that allow you to manipulate and modify data.

Part IV, SQL in PL/SQL

Chapter 13 through Chapter 15 address one of the most central elements of PL/SQL code construction: the connection to the underlying database, which takes place through SQL. These chapters show you how to define transactions that update, insert, and delete tables in the database; how to query information from the database for processing in a PL/SQL program; and how to execute SQL statements dynamically, using native dynamic SQL (NDS), which was introduced in Oracle8i.

Part V, PL/SQL Application Construction

This is where it all comes together. You know about declaring and working with variables, and you’re an expert in error handling and loop construction. Now, in Chapter 16 through Chapter 19, you’ll learn about the building blocks of applications, which include procedures, functions, triggers, and packages. Chapter 19 discusses how to manage your PL/SQL code base, including suggestions for tuning and debugging that code.

Part VI, Advanced PL/SQL Topics

A language as mature and rich as PL/SQL is full of features that you may not use on a day-to-day basis, but that may sometimes be the difference between success and failure. Chapter 20 contains an exploration into the PL/SQL runtime architecture, including PL/SQL’s use of memory and the differences between server-side and client-side PL/SQL. Chapter 21 offers an in-depth guide to the object-oriented features of Oracle (object types and object views). Chapter 22 and Chapter 23 show you how to invoke Java and C code from your PL/SQL applications.

If you are an accomplished programmer who is just new to PL/SQL, reading this book from beginning to end should improve your skills and deepen your understanding. If you’re already a proficient PL/SQL programmer, you’ll probably want to dip into the appropriate sections to extract particular techniques for immediate application. Whether you use this book as a teaching guide or as a reference, we hope that it will help you to use PL/SQL effectively.

What This Book Does Not Cover

Long as this book is, it doesn’t contain everything. The Oracle environment is huge and complex, and in this book we’ve focused our attention on the core PL/SQL language itself. The following topics are therefore outside the scope of this book and are not covered, except in an occasional and peripheral fashion:

The SQL language

We assume that you already have a working knowledge of the SQL language, and that you know how to write SELECTs, UPDATEs, INSERTs, and DELETEs.

Administration of Oracle databases

While DBAs 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

We don’t cover detailed tuning issues in this book, although Chapter 19 does discuss the tracing, tuning, and debugging of PL/SQL programs. There are other books that delve deeply into Oracle application tuning.

Oracle tool-specific technologies independent of PL/SQL

This book does not attempt to show you how to build applications in a tool like Oracle’s Forms Developer, even though the implementation language is PL/SQL. We have chosen to focus on core language capabilities, centered on what you can do with PL/SQL from within the database. However, most everything covered in this book is applicable to PL/SQL inside Forms Developer and Reports Developer.

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.