PL/SQL Exception handling in procedures

Question

This is kind of a question about best practices. I have a PL/SQL block similar to this

DECLARE

    --work variables

    PROCEDURE p1(in_parameter1, out_parameter1, out_parameter2...) IS
    BEGIN    
        --do stuff
        --select ( ... ) into ( ... ) from t1 where ( ... )
    END;

    PROCEDURE p2(in_parameter1, out_parameter1, out_parameter2...) IS
    BEGIN 
        --do stuff
        --insert/update tables
        --do more stuff
    END;    

BEGIN -- MAIN PROCESS STARTS HERE

    open c1;
    fetch c1 into c1RowData;
    EXIT WHEN c1%NOTFOUND    
     --call procedure1
     --do stuff
     --call procedure2
     --do stuff
     --do stuff
     --call procedure1
     --call procedure2
END;
/
EXIT;

The statements in procedures p1 and p2 could possibly raise exceptions ( NO_DATA_FOUND, DUP_VAL_ON_INDEX, ... ).

What do you think is the best way to handle this exceptions? Should they be handled inside the procedures or do you think that I should surround every call the procedures in the main body with a TRY-CATCH block?

Answer 1

You should try to handle exceptions at the source (i.e. within the procedure that raises them). This gives you greater scope for reporting where the issue occurred and, usually, a greater chance of being able to gracefully rectify the issue without passing a nasty Oracle error message up the stack to the user.

Of course you can both handle the error and re-raise it if you really needed to but as StevieG answered you can also raise user defined exceptions which are generally more application specific and more helpful to your users/other PL/SQL code.

There is an ASKTOM discussion on custom error handling here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4684561825338

Answer 2

Personally I'd catch them inside the procedures they're thrown from. This means that you have much more control with regards to how they're handled externally. For example, you can throw them again as user defined exceptions which you can embellish with more information about exactly what went wrong.

'Failed to find a matching row in table a for value b'

is much more descriptive outside the procedure than

'no data found'

But this is realy dependent on:

1. The error reporting requirements of the calling application
2. The actual functionality implemented where you 'do stuff' within the procedures

For example, say you wanted to run procedure 2 even though the select in procedure 1 found no rows.. You'd need to catch the exception in procedure 1 and ignore it. If you didn't, then it'd be thrown to the exception handler in procedure 2.

Or, say you wanted procedure 1 to insert a row in the case where the select didn't find anything, in this case you'd need to catch the exception and perform the insert in the exception handler.

Now, before anyone jumps on me, I'm not recommending you use exception handlers for contrlling the flow of execution within your code, these examples are the theoretical, but hopefully you get the idea..

Answer 3

It is best when the exceptions handled inside the procedure. It will be resulting quick. Not only that if we reuse the same procedure in some other functions, there also we don't need to handle exceptions. I my point of view, exceptions can handle inside the procedure itself is the best one.