1. Chapter 1 The Development Process

2. Chapter 2 Coding Style and Conventions

3. Chapter 3 Variables and Data Structures

   1. Declaring Variables and Data Structures

   2. Using Variables and Data Structures

   3. Declaring and Using Package Variables

4. Chapter 4 Control Structures

   1. Conditional and Boolean Logic

   2. Loop Processing

   3. Miscellaneous

5. Chapter 5 Exception Handling

   1. EXC-00: Set guidelines for application-wide error handling before you start coding.

   2. Raising Exceptions

   3. Handling Exceptions

   4. Declaring Exceptions

6. Chapter 6 Writing SQL in PL/SQL

   1. SQL-00: Establish and follow clear rules for how to write SQL in your application.

   2. General SQL and Transaction Management

   3. Querying Data from PL/SQL

   4. Changing Data from PL/SQL

   5. Dynamic SQL and Dynamic PL/SQL

7. Chapter 7 Program Construction

   1. Structure and Parameters

   2. Functions

   3. Triggers

8. Chapter 8 Package Construction

9. Chapter 9 Built-in Packages

   1. DBMS_OUTPUT

   2. UTL_FILE

   3. DBMS_PIPE

   4. DBMS_ JOB

1. Appendix A Best Practices Quick Reference

   1. The Development Process

   2. Coding Style and Conventions

   3. Variables and Data Structures

   4. Control Structures

   5. Exception Handling

   6. Writing SQL in PL/SQL

   7. Program Construction

   8. Package Construction

   9. Built-in Packages

2. Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The animal on the cover of Oracle PL/SQL Best Practices is a red wood ant. Red wood ants (Formica aquilonia) are often the dominant ants of forests throughout the northern hemisphere. F. aquilonia can build nest mounds of dried spruce needles and twigs that are three feet or more in diameter and height. Each nest can contain thousands of ants as well as several queens. The insects have no sting but can defend themselves by firing formic acid from their rear ends when disturbed.

The workers vary in size up to about 1/2 inch in length with a red thorax, black abdomen, and red and black marked head. The ants are both scavengers and general predators of insects, carrying many soft-bodied caterpillars, flies, and sawflies along their several major trails back to the nest.

Red wood ants are a keystone species (i.e., without them the ecosystem changes fundamentally). When red ants disappear from a system, herbivorous insects can subsequently damage forest trees. In forests weakened by pollution and acid rain in central Europe, red wood ant populations are often endangered, which in turn causes further imbalances in predator-prey dynamics and the ecosystem. These rare ants are protected by law in some European countries because of their great value in destroying forest pests.

For 28 years, Professor Seigo Higashi has been studying a supercolony of Japanese red wood ants (Formica yessensis), which dwell along a strip of shoreline on the Ishikari coast of northern Japan. When first discovered in 1973, the colony consisted of approximately 45,000 nests with connecting tunnels extending nearly 12.4 miles along the shore of the Japan Sea. It was estimated that the colony had about 306 million workers and 1.1 million queens, and is thought to be about 1,000 years old. Since 1973, the colony has been under siege, threatened by the development of infrastructure for a new port on Ishikari Bay, which has occurred on top of 30% of the ant megalopolis. This has reduced the number of red wood ants living there by more than half.

The Ishikari ants are one of only two known ant supercolonies in the world. The other, smaller one is in the Swiss Jura mountains. Mary Anne Weeks Mayo was the production editor, and Clairemarie Fisher O'Leary was the copyeditor for Oracle PL/SQL Best Practices. Mary Sheehan, Matt Hutchinson, and Jane Ellin provided quality control. Rachel Wheeler and Gabe Weiss provided production assistance.

Ellie Volckhausen designed the cover of this book, based on a series design by Edie Freedman. The cover image is a 19th-century engraving from the Dover Pictorial Archive. Emma Colby produced the cover layout with QuarkXPress 4.1 using Adobe's ITC Garamond font.

David Futato designed the interior layout based on a series design by Nancy Priest. Clifford Dyer and Anne-Marie Vaduva converted the files from Microsoft Word to FrameMaker 5.5.6 using tools created by Mike Sierra. The text and heading fonts are ITC Garamond Light and Garamond Book. This colophon was compiled by Mary Anne Weeks Mayo.