Oracle PL/SQL Programming, 5th Edition

Structure of This Book

Both the authors and O’Reilly Media are committed to providing comprehensive, useful coverage of PL/SQL over the life of the language. This fifth edition of Oracle PL/SQL Programming describes the features and capabilities of PL/SQL up through Oracle Database 11g Release 2. I assume for this edition that Oracle Database 11g is the baseline PL/SQL version. However, where appropriate, I reference specific features introduced (or only available) in other, earlier versions. For a list of the main characteristics of the various releases, see the section About PL/SQL Versions in Chapter 1.

PL/SQL has improved dramatically since the release of Version 1.0 in the Oracle 6 database so many years ago. Oracle PL/SQL Programming has also undergone a series of major transformations to keep up with PL/SQL and provide ever-improving coverage of its features.

The fifth edition offers the following new content:

Oracle Database 11g new features for PL/SQL

As explained above, this book incorporates all new PL/SQL features in Oracle Database 11g Releases 1 and 2. The major features are summarized in Chapter 1, along with references to the chapters where these features are discussed in detail.

Optimizing PL/SQL Performance

Chapter 21 is new in the fifth edition. It collects together previous content in this book on optimizing the performance of PL/SQL code, and then adds lots of new information not previously available in Oracle PL/SQL Programming.

I am very happy with the results and hope that you will be too. There is more information than ever before, but I think we managed to present it without losing that “trademark” sense of humor and conversational tone that readers have told me 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, fourth, and fifth editions, 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 lead author is represented by the “I” in a given chapter, we thought we’d offer this quick guide for the curious; you’ll find additional discussion of our contributors under the Acknowledgments.

Chapter	Author	Chapter	Author
Preface	Steven	15	Steven
1	Steven	16	Steven
2	Bill and Steven	17	Steven
3	Steven and Bill	18	Steven
4	Steven, Chip, and Jonathan	19	Darryl and Steven
5	Steven and Bill	20	Steven
6	Steven	21	Steven and Adrian
7	Chip, Jonathan, and Steven	22	Bill and Steven
8	Chip, Jonathan, and Steven	23	Arup
9	Chip, Jonathan, and Steven	24	Bill, Steven, and Chip
10	Chip, Jonathan, and Steven	25	Ron
11	Steven	26	Bill and Steven
12	Steven and Bill	27	Bill and Steven
13	Chip and Jonathan	28	Bill and Steven
14	Steven

About the Contents

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

Part I

I start from the very beginning in Chapter 1: where did PL/SQL come from? What is it good for? I offer a very quick review of some of the main features of the PL/SQL language. Chapter 2 is designed to help you get PL/SQL programs up and running 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 a PL/SQL statement, an introduction to the block structure, how to write comments in PL/SQL, and so on.

Part II

Chapter 4 through Chapter 6 explore conditional (IF and CASE) and sequential (GOTO and NULL control statements; loops and the CONTINUE statement introduced for loops in Oracle Database 11g; 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

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 13 concentrate on the various types of program data you can define in PL/SQL, such as numbers, strings, dates, timestamps, records, and collections. You will learn about the new datatypes introduced in Oracle Database 11g (SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE), as well as the many binary, date, and timestamp types introduced in other recent releases. These chapters also cover the various built-in functions provided by Oracle that allow you to manipulate and modify data.

Part IV

Chapter 14 through Chapter 16 address one of the central elements of PL/SQL code construction: the connection to the underlying database, which takes place through the SQL language. These chapters show you how to define transactions that update, insert, merge, 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).

Part V

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 17 through Chapter 22, you’ll learn about the building blocks of applications, which include procedures, functions, packages, and triggers, and how to move information into and out of PL/SQL programs. Chapter 20 discusses managing your PL/SQL code base, including testing and debugging programs and managing dependencies; it also provides an overview of the edition-based redefinition capability introduced in Oracle Database 11g Release 2. Chapter 21, new in the fifth edition, focuses on how you can use a variety of tools and techniques to get the best performance out of your PL/SQL programs. Chapter 22 covers I/O techniques for PL/SQL, from DBMS_OUTPUT (writing output to the screen) and UTL_FILE (reading and writing files) to UTL_MAIL (sending mail) and UTL_HTTP (retrieving data from a web page).

Part VI

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 make the crucial difference between success and failure. Chapter 23 explores the security-related challenges we face as we build PL/SQL programs. Chapter 24 contains an exploration of the PL/SQL architecture, including PL/SQL’s use of memory. Chapter 25 provides guidance for PL/SQL developers who need to address issues of globalization and localization. Chapter 26 offers a guide to the object-oriented features of Oracle (object types and object views).

Appendixes A through C summarize the details of regular expression syntax and number and date formats.

In this fifth edition, the chapters on invoking Java and C code from PL/SQL applications, which were part of the hardcopy fourth edition, have been moved to the book’s web site.

If you are new to PL/SQL, reading this book from beginning to end should improve your PL/SQL skills and deepen your understanding of the language. 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, I hope it will help you 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

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

Administration of Oracle databases

While database administrators (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

I don’t cover detailed tuning issues in this book, although Chapter 21 does discuss the many tools and techniques that will help you to optimize the performance of your PL/SQL programs.

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. I 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.