Wednesday, June 17, 2009

The Humble PL/SQL Exception (Part 1) - The Disappearing RETURN

Exception handling in PL/SQL is a big subject, with a lot of nuances. Still, you have to start somewhere. Let's take one simple use case for exceptions, and see if it leads to some thoughts about best practices. (Hopefully, this is not the last post in this particular series.)

One common pattern I find in PL/SQL procedures is a series of tests early on...

if not_supposed_to_even_be_here() then
end if;

if no_data_to_process() then
end if;

if no_parameters_passed() then
end if;

In real life, these tests tend to use hardcoded constants, queries, etc. that clutter up the procedure and make it hard to follow, rather than descriptive names as in the example above. One simple solution is to move the whole block into its own inner procedure:


These procedures, declared with the procedure ... is ... syntax immediately before the begin of the main procedure, can access all the variables from the main procedure, so they typically don't require parameters. In most cases, you can just lift a block of confusing code from the main procedure, and turn it into an inner procedure with a descriptive name.

However, the return statement complicates things. When transplanted into an inner procedure, it loses its mojo. Instead of cutting short the entire procedure, it becomes essentially a no-op, because now it's at the end of a short inner procedure that was about to return anyway, back to the middle of the main procedure. The solution is to use an exception, which requires structuring the whole business like so:

create or replace procedure big_procedure as
num_rows number;
exception skip_normal_processing;
procedure check_data_to_process is
select count(*) into num_rows from data_table;
if num_rows = 0 then
raise skip_normal_processing;
end if;
check_data_to_process(); all the normal stuff if there really is data to process...
when skip_normal_processing then null;

Now if you detect some condition that means the procedure should bail out, it really will. If you can anticipate that your procedures might get lengthy enough to benefit from using inner procedures this way, you can plan ahead by using exceptions right from the start, instead of starting with return statements and then turning them into exceptions when you restructure the original straight-line procedure.

One thing that still bothers me is the way the control flow jumps around. The calls to the inner procedures jump backwards, and if any "stop! now!" conditions are triggered, control jumps forward all the way to the end of the main procedure. When I visualize such a structure, it reminds me just a little of spaghetti code. I know that on paper, all is as it should be -- all the reusable / modular code is separated out at the front, all the error handling and termination code is separated out at the end. I just would like to see more real-life cases where such structure saves on maintenance and debugging time, before passing final judgment.


oraclenerd said...


I'm going to have to disagree with you on the internal procedure (in the declaration section) paradigm. What about testing? How would you go about testing that internal procedure? The answer is that you cannot (very much like private procedures/functions inside of packages...which I'm still wrestling with).

Let's say you have a single procedure and it's really long. You want to modularize it by using private/internal procedures (as you don't have to pass parameters, it is a nice thing). Now you have 2000 lines in your declaration section and 400 or so in the body. (I've seen it...really, I've seen it). Then you want to change one of those internal can't test it without testing the entire thing.

Just my opinion any way.


Brian Tkatch said...

Personally, i have done this (with a PACKAGE though) by RETURNing a value from the PROCEDURE, and then checking it in the main code:

IF Some_check() = 1 THEN RETURN; END IF;

It's not as pretty, but i found it to workout nicely.