When writing Production-quality software, take the attitude of making all your code bullet-proof right up-front, the first time through coding (instead of thinking "I'll add it later"). This requires a tedious amount of pessimism about your own code. It seems like overkill while you're writing it, but it'll really save your butt.

1. Enclose all implicit SQL in their own blocks with exception handlers. Procedurize them for readability and modularity, which leads to maintainability.
2. Inside an exception handler, if you have implicit SQL or procedure calls that may also generate exceptions, put a block around them (with a NULL exception handler), to avoid infinite loops.
3. Use some sort of tracing or debugging method to localize values involved in the error. For example, in a procedure that encapsulates an implicit SELECT, in its exception handler I optionally write the IN parameters to an error log table, in addition to capturing the SQLCODE and module name. It's not enough to know you had an error -- you need to also know what record suffered the error. This technique makes it far easier to troubleshoot each module post mortem.
4. Your main outermost block should have its own exception handler. Never return an unhandled exception to the calling context, unless it's by design (for example, so a UNIX shell script can be terminated).
5. Perform as much data validation as is sensible, especially on input parameters. What if NULL is passed in? What if the start date is higher than the end date? Ask yourself these questions and decide what action should be taken for "funny" values.
6. Watch out for what assumptions you make. For example, don't take it for granted that a particular record lookup will succeed. Document your assumptions inline to your code. 2 years from now nobody'll remember why things were written that way.
7. Know your data. Run some queries to get counts, discrete values, are there any NULL values in there, boundary values, etc. You may find yourself coding around certain data problems.
8. Have default actions for situations that you "know" will never happen. For example, let's say you're testing a variable for equality to a string:

IF (local_var = 'YES') THEN
...
ELSIF (local_var = 'NO') THEN
...
ELSIF (local_var = 'MAYBE') THEN
...
ELSE -- should never happen!
...
END IF;

The final test, which would be an unexpected error, handles the unexpected. Without it, should the situation arise, you would never know, and the program would behave in an unanticipated manner. If anybody can think of any other trouble-shooting guidelines, or disagrees with mine, send them in! I could go on for days with horror stories related to "expedient" programming. Here's a quick one:

A programmer dispensed with exception handlers for implicit queries (SELECT INTO). So I made her put them in. Then I saw she had simply coded a NULL statement for each exception handler! So I made her write the SQLERRM for the SQLCODE value returned, to a log table. On the next run, we examined the log table and discovered she was getting a VALUE_ERROR on certain rows, so that the value from the previous row was still being used (the variable being Selected INTO is not written to on an error)! She fixed the bug and got very different results!

************************************************************