How to raise_application_error beyond a when others clause

exceptionoracleplsql

Background:

I've used a few Oracle articles to develop an error package, with some procedures. One of these is Log_And_Return which is called throughout the database. I've simplified it for brevity, and it looks like this:

PROCEDURE Log_And_Return (error_name)
IS
BEGIN
    // Some code to convert error_name to error_code and error_message
    LOG (error_code, error_message);
    Raise_To_Application (error_code, error_message);
END Log_And_Return;
// In reality, this procedure takes parameters which determine whether it will log and/or raise.

The Log procedure does as it says and the Raise_To_Application procedure simply raises an application error.

Problem:

I have a procedure, which performs a query, e.g. fetching a customer record. If this query fails, it's a big problem. So I could do this:

BEGIN    
    SELECT *something*
    FROM *some table*
    WHERE *some field* = *some user input*

EXCEPTION
WHEN NO_DATA_FOUND THEN
    ERR.Log_And_Return('unknown_id');  
WHEN OTHERS THEN
    ERR.Log_And_Return('unknown_error');  
END;

So I'm displaying a specific error message if no data is found, or a generic one is another problem has occurred.

Now, let's say I amend the code:

BEGIN    
    SELECT *something* INTO *some variable*
    FROM *some table*
    WHERE *some field* = *user id*
    Call_Another_Procedure(*user id*, *some variable*)

EXCEPTION
WHEN NO_DATA_FOUND THEN
    ERR.Log_And_Return('unknown_id');  
WHEN OTHERS THEN
    ERR.Log_And_Return('unknown_error');  
END;

Now, after the select query, I'm calling a new procedure. Inside it, I'm doing a few things, including an update statement, like so:

// bunch of logic

BEGIN
    UPDATE *another table*
    SET *some field* = *some value*
    WHERE *some field* = *variable passed into method*
EXCEPTION
WHEN NO_DATA_FOUND THEN
    Err.Log_And_Return('some_error')
END;

Question:

If the no_data_found error is thrown, Log_And_Return will log the problem and raise an application error… which will then be caught by the when others clause in the parent procedure – which will return the wrong message to the user.

What is the workaround to this?

One workaround I had considered, and I've no idea if this is recommended or not, would be to wrap every stored procedure with a BEGIN END EXCEPTION block, where every procedure had a when others block that just logged and re raised the most recent error (using SQLCODE). Then, in my application layer I could specify that if the error is between -20000 and -20999, show it along with its message, otherwise show a generic message (and the DBA can find out what happened in the database by looking at the log table, along with a full stacktrace).

Or do I simply get rid of ALL when others blocks and let the application layer deal with any of these unknown exceptions?

Best Answer

There are a variety of approaches you could take. You should pick the one you feel is most suitable for your application.

  1. Include no WHEN OTHERS exception handlers and let the raw Oracle errors propagate to your users.
  2. Include WHEN OTHERS exception handlers in the top-level blocks.
  3. Include WHEN OTHERS exception handlers in all/most blocks

The first option requires no effort on your part, but can expose users to very unfriendly error messages. They can also expose details about the database which could be of use to hackers, etc.

The second option require some effort. You can use the built-in packages, such as DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to identify where an exception occurred, which can be included in any logging processes you may have. You can convert the raw Oracle errors into application specific ones.

The third option requires most effort. However, as well as the above, you can also log items such as variable values to help you identify why an error occurred, not just where it occurred. Using some common exception handling routines -as you seem to be doing - will reduce the effort required for this approach.

WHEN OTHERS THEN
   exception_pkg.handler('<variable_values_of_interest>')
   RAISE;

where you pass the variables values to be logged via a standard process before re-raising the exception.

You should always re-raise an exception in a WHEN OTHERS block, either the original exception or one with an application specific message - via RAISE_APPLICATION_ERROR.

Related Question