3

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?

4
  • Which error codes do you use in Log_And_Return to re-raise errors?
    – Dmitriy
    Nov 20, 2014 at 20:36
  • @Dmitry: I have a query which calls a table and pulls an error code and error message depending on the name of the error fed to the Log and Return procedure. All of these number between -20000 and -20999. Nov 20, 2014 at 20:39
  • This is a multi-post (aka cross-post) of stackoverflow.com/q/27040996/272735. Please do not multi-post: meta.stackexchange.com/q/64068
    – user272735
    Nov 21, 2014 at 5:54
  • @user272735 Apologies. I was going to delete the original but it had answers so I felt that was inappropriate. Therefore I tried to phrase the question a little differently in the hope it would be "new" enough. I'll not do it in future. Nov 21, 2014 at 8:47

3 Answers 3

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.

2
  • But how do I solve my issue in the problem above? Using your example, If I raise a custom error message in the subprocedure, the parent procedure will catch it in the "when others" block, change the error message in the "handler" procedure and then raise something different to what was thrown in the original subprocedure. How do I avoid this? Nov 20, 2014 at 16:15
  • The handler I suggest would not change the error message, it would merely log any information of interest, such as the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and variable values. If you raised a custom error message - presumably via RAISE_APPLICATION_ERROR - in a sub-procedure this would be re-raised as-is in the WHEN OTHERS block, and so on up the call stack. The original exception would eventually propagate from the topmost block. I would not have a Raise_To_Application call in the Log_And_Return.
    – DrabJay
    Nov 20, 2014 at 16:40
0

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.

2
  • I'm actually starting to lean more towards this option. I'm considering actually having all procedure calls in stand alone "begin/end" blocks, so that if any exception is thrown within them it will automatically be thrown back to application layer. Nov 20, 2014 at 16:28
  • I think what you're saying could be used to help do that. Nov 20, 2014 at 16:29
0

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.

2
  • The problem with this is I would have to declare an exception for EVERY possible exception that could be raised. And there really will be A LOT (>700 stored procedures) Nov 20, 2014 at 21:51
  • @AndrewMartin You can use procedure like errpkg.raise in Steven Feuerstein's article to unite them in groups...
    – Dmitriy
    Nov 20, 2014 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.