Table of Contents


When I first started using the Oracle relational database in 1991, I had no idea what I was doing or how important this tool would become to my future. Now, nearly six years later, I’ve written hundreds of thousands of lines of code in SQL and PL/SQL.

Oracle is an important product that drives a majority of the world’s largest databases and runs on numerous operating systems, including most flavors of Unix. Oracle, perhaps more so than other relational databases, is a flexible tool that allows businesses to define their operations and build effective systems around them.

“Database automation” seems like a murky topic, but in reality it is very straightforward. Scripting is the very important first half of database automation, allowing database administrators and application developers to perform complex tasks on a regular basis with a minimum of fuss and a high level of confidence and repeatability.

The use of stored PL/SQL code to enforce business rules at the database level is the second half of database automation. Database triggers, procedures, functions, and packages can all play important roles when implementing a complex system, drastically reducing the amount of code that must be written for the system’s front end.

The purpose of this book is not to teach the syntax of SQL and PL/SQL coding, but to emphasize the concepts behind the code. Toward this end, I’ve emphasized designing, documenting, and testing code as the most important duties of the application developer. If you need to know syntax, look at the manual. Technique is the most important weapon an application developer has against a dragon of a system.

Chapter 1 is an introduction to the central concepts of database automation. The chapter also discusses some long-term advancements that will make database automation even more powerful. Chapter 2 is a primer on Oracle, SQL, and PL/SQL for readers who are new to Oracle or to those who have limited experience with the database.

Chapters 3 through 7 emphasize the concepts behind designing, documenting, coding, and testing scripts, stored procedures, stored functions, packages, and database triggers. Emphasis is given to design, documentation, and testing over syntax.

Chapter 8 contains essential debugging skills for your code, with an emphasis on the best methods to locate runtime errors and write code that is easily debugged and maintained. Chapter 9 discusses the packages that are provided by Oracle and presents real-world examples of how to use these tools. Finally, Chapter 10 discusses tips and techniques that you can use to get the best performance out of your SQL and PL/SQL code. I’ve also included a look at some of Oracle8’s new features, and a sample PL/SQL coding standard that can be customized for your place of business.

I’ve tried to cover the essentials first and then include whatever else seemed most useful, but with a project of this size you always feel that there’s more to be done. If you would like to comment on the book, point out typos or inconsistencies, or simply add an encouraging word to the mix, I welcome your comments at

Table of Contents