1. Chapter 1 The Big Picture

   1. Successful Applications Are Never an Accident

   2. Best Practices for Successful Applications

   3. Software is like ballet: choreograph the moves or end up with a mess.

   4. Deferred satisfaction is a required emotion for best practices.

   5. Contracts work for the real world; why not software, too?

   6. Don't act like a bird: admit weakness and ignorance.

   7. Five heads are better than one.

   8. Don't write code that a machine could write for you instead.

   9. We need more than brains to write software.

2. Chapter 2 Real Developers Follow Standards

   1. Best Practices for Developing and Using Standards

   2. It's a free country; I don't have to use carriage returns in my code.

   3. Too much freedom is a very bad thing.

   4. Good names lead to good code.

   5. Put your checklists into your code.

   6. Who needs comments? My code is self-documenting!

3. Chapter 3 Life After Compilation

   1. Testing, Tracing, and Debugging

   2. Best Practices for Testing, Tracing, and Debugging

   3. Thanks, but no thanks, to DBMS_OUTPUT.PUT_LINE!

   4. Assume the worst, and you will never be disappointed.

   5. Users really don't want to be programmers.

   6. Do you take road trips without a destination in mind?

   7. For every test you can think of, there are 10 tests waiting to be performed.

   8. Sherlock Holmes never had it so good.

4. Chapter 4 What's Code Without Variables?

   1. Best Practices for Declaring Variables and Data Structures

   2. That column's never going to change!

   3. There's more to data than columns in a table.

   4. I take exception to your declaration section.

   5. Best Practices for Using Variables and Data Structures

   6. This logic is driving me crazy!

   7. Go ahead and splurge: declare distinct variables for different usages.

   8. Didn't your parents teach you to clean up after yourself?

   9. Programmers are (or should be) control freaks.

   10. Best Practices for Declaring and Using Package Variables

   11. Danger, Will Robinson! Globals in use!

   12. Packages should have a strong sense of personal space.

5. Chapter 5 Developer As Traffic Cop

   1. Best Practices for Conditional and Boolean Logic

   2. Reading your code should not require mental gymnastics.

   3. KISS (Keep it Simple, Steven).

   4. Beware the hidden costs of NULL.

   5. Best Practices for Loop Processing

   6. There's a right way and a wrong way to say goodbye.

   7. Don't take out "programmers' insurance" . . . and don't worry about SkyNet.

   8. There is more than one way to scan a collection.

   9. Best Practices for Branching Logic

   10. Maze-like programs are never a good thing.

6. Chapter 6 Doing the Right Thing When Stuff Goes Wrong

   1. Best Practices for Understanding Error Handling

   2. Ignorance is bad exception management.

   3. All exceptions are not created equal.

   4. One error management approach for all.

   5. Best Practices for Nitty-Gritty, Everyday Exception Programming

   6. Your code makes me feel dumb.

   7. Avoid programmer apathy.

   8. Best Practices for Coding Defensively

   9. You weren't supposed to do that with my program!

7. Chapter 7 Break Your Addiction to SQL

   1. SQL Is Bad!

   2. General SQL Best Practices

   3. The best way to avoid problematic code is to not write it.

   4. You may write PL/SQL code, but SQL always takes precedence.

   5. When one transaction is not enough.

   6. I don't always want to save my changes.

   7. Best Practices for Querying Data from PL/SQL

   8. It's always better to fetch items into a single basket.

   9. Answer the question being asked; that is, be a good listener.

   10. Your code makes my head spin.

   11. Best Practices for Changing Data from PL/SQL

   12. Assume the worst!

   13. Things only get more complicated over time.

   14. Timing is everything in the world of cursors.

   15. Best Practices for Dynamic SQL

   16. Make it easy to untangle and debug your dynamic SQL statements.

   17. Give the RDBMS a break.

   18. So you think you know what users might do with your code?

   19. It's rude to drop someone else's objects.

8. Chapter 8 Playing with Blocks (of Code)

   1. Best Practices for Parameters

   2. Once a program is in use, you can't change it willy-nilly.

   3. What the heck do those parameter values mean?

   4. Where'd that data come from?

   5. Best Practices for Procedures and Functions

   6. Write tiny chunks of code.

   7. Every program should be an island (of purpose).

   8. Gifts should always come tightly wrapped.

   9. One way in, one way out: multiple exits confuse me.

   10. Black or white programs don't know from NULL.

   11. Best Practices for Packages

   12. Where there is one program, there will soon be two.

   13. "Easy to use code" is code that is used—and reused.

   14. Best Practices for Triggers

   15. Uncertainty in trigger execution is a most unsettling emotion.

   16. "One-stop triggering" is so much easier to understand and maintain.

   17. Your application should not be able to perform a "Houdini" with business rules.

9. Chapter 9 My Code Runs Faster Than Your Code

   1. Best Practices for Finding Slow Code

   2. Take the guesswork out of optimization.

   3. There are so many ways to implement an algorithm; which is best?

   4. Best Practices for High-Impact Tuning

   5. Let Oracle do most of the tuning for you.

   6. Who has time for querying (or inserting or deleting or updating) one row at a time?

   7. If the SGA is so wonderful, why not emulate it?

   8. When waiting is not an option...

   9. Best Practices for Other Tuning

   10. Sometimes "safe programming" is a little too slow.

   11. PL/SQL loops should not resemble hamsters running in circles.

   12. Tailor-made datatypes are the best fit for your programs.

1. Appendix Best Practices Quick Reference

2. Appendix Resources for PL/SQL Developers

   1. Book Example Files

   2. Books and Other Content

   3. Free Software

3. Colophon

The animal on the cover of Oracle PL/SQL Best Practices, Second Edition, 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 half an 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 percent 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.