How to raise_application_error beyond a when others clause

Question

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?

Answer 1

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.

Answer 2

This might be over simplifying it, but could you use an IF *some variable* IS NOT NULL block around the call to the extra procedure? As in, "check to see if the variable I just wrote to is null, if not, do more work, if it is, specifically call my custom error package?

If you've built your own exception handler (and kudos to you for not falling into the "catch all the errors generically" trap), you don't HAVE to call that in an exception block. You can call it whenever you want, and trap the errors whenever they occur.

Answer 3

Maybe you need something like this?

create or replace procedure p1 is
begin
  select ... into ...
  from ... where ...;
exception
  when no_data_found then
    ...
    raise_application_error(-20100, 'Something not found');
  when others then
    ...
end;

create or replace procedure p2 is
  something_not_found exception;
  pragma exception_init(something_not_found, -20100);
begin
  p1;
exception
  when something_not_found then
    -- after NO_DATA_FOUND exception in p1 this code will be executed
    ...
  when others then
    ...
end;

Also you can define exception names and use pragma exception_init in package specification.