Best Practices for Error Handling in Database Stored Procedures

I recently performed an assessment of a large set of Oracle stored procedures and batch scripts that a client uses as part of their build and update process. The problem was that the build process was not very stable. During each build, something would go wrong, such as a table not being created or loaded as it should be, and it would take the development team a long time to figure out just what went wrong.

When doing an assessment like this, I look to see how well the code is formatted, structured (modularized), documented, and organized. I also look to see if it is robust (that is, it can gracefully handle failure), and how well the process is logged.

In this case, I found that the processes that were followed were well documented and well modularized. The batch scripts were organized well and used a good naming convention that made it pretty easy to understand the process flow. The PL/SQL stored procedures themselves were quite well written. They were formatted nicely and properly used some of the more sophisticated capabilities of Oracle PL/SQL, such as cursor variables and CURSOR FOR LOOPs when necessary. The developer clearly had a mastery of SQL and of PL/SQL.

But there was one glaring hole in the quality of the work. There was absolutely no exception handling or logging anywhere in the entire process. Not one stored procedure or PL/SQL script checked for errors.

So even though the build system itself was developed by a highly skilled developer, the absence of good exception handling diminished the quality of the whole system. There are two basic exception handling steps that I think should be placed in all PL/SQL code, and some that should be in any large or repeating process.

Here are a couple of the more important ones:

Include an exception handler for every logical grouping of PL/SQL statements (BEGIN/END block). This does two things: First, it forces the developer to think about the edge cases that might go wrong so they can handle them appropriately. This alone improves the quality of the code and the stability of the system. Second, it offers an opportunity to determine at what level the exception should be handled. This is critically important to ensuring that the process continues when it should and stops when it should not. This is especially important when nesting procedures or PL/SQL scripts.
Log everything, especially exceptions. By creating a separate logging procedure that you call at the beginning, at various points in within the code, and in exception blocks, you can always know exactly which processes completed before a failure. An added benefit of this approach is that the record of when each process took place helps a great deal with performance tuning because you can easily tell which processes are taking the most time.A procedure like this might take the the following parameters:
- The name of the stored procedure it is being called from
- An indicator of the position in the stored procedure in that procedure
- A text string that consists of a concatenation of the values of key variables that are used in the procedure
- A text string indicating the error if called within an exception block

These parameter values are simply written to a table and optionally to a text file via DBMS_OUTPUT. This helps enormously in troubleshooting later, and it is very easy to do. Write the logging procedure once and call it from everywhere.

There are other steps that you can take to enhance your exception handling further. But the simple steps I just described will significantly improve the stability of your code and your ability to find and resolve any errors that do arise.

If you have used other strategies to create robust, reliable code, please comment and share your ideas.

For expert advice on how to fine-tune use of Oracle’s recycle bin for your environment, schedule a free consultation with Buda Consulting.