Shared code for multiple specific exceptions in PL/SQL

2
votes

I am fairly new to PL/SQL and am trying to implement some exception handling for a package I have written.

I have come across a situation where I have multiple exceptions that may be raised.

In the event these exceptions are raised I may want to do something specific for each of the exceptions, for example if exception A is raised then close and delete a file, but if exception B is raised then I need only close a cursor and send an email to warn someone that a fatal error has occurred.

This is fine and I understand how to use WHEN <EXCEPTION_NAME> THEN.

The problem I am having is that I can't write generic code which occurs for any exception raised to do something I will always want done in the event of an exception, for example writing to the log file. This means that I have to duplicate lines of code for each exception type as shown below.

DECLARE
    test_exception EXCEPTION;
BEGIN
    --some code
    RAISE test_exception;
    --some code
EXCEPTION        
    WHEN test_exception THEN        
        SEND_EMAIL('Something went wrong');
        WRITE_TO_LOG('Error ' || SQLCODE || ' | ' || SUBSTR(SQLERRM, 1, 200) || ' | ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    WHEN OTHERS THEN     
        SOME_OTHER_FUNCTION();
        WRITE_TO_LOG('Error ' || SQLCODE || ' | ' || SUBSTR(SQLERRM, 1, 200) || ' | ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); 
END;

What I would like to achieve is something similar to this, which does not compile.

DECLARE
    test_exception EXCEPTION;
BEGIN
    --some code
    RAISE test_exception;
    --some code
EXCEPTION        

    WRITE_TO_LOG('Error ' || SQLCODE || ' | ' || SUBSTR(SQLERRM, 1, 200) || ' | ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

    WHEN test_exception THEN        
        SEND_EMAIL('Something went wrong');
    WHEN OTHERS THEN      
        SOME_OTHER_FUNCTION();
END;

Obviously this example isn't complete but gives a rough idea. For a single duplicated line like this it isn't a problem, but if there are many cursors or files to close or other housekeeping this seems a bit verbose/tedious.

Is there a WHEN ALL THEN clause or similar? Would this be a use case for some kind of GOTO? Or am I trying to apply the wrong sort of concept here.

I can't seem to find people asking the same question which to me means I am approaching the issue in the wrong way or I am missing some basic knowledge.

Thanks

2
oracleplsql
Yeah, I have struggled with this for years. I big hole I think in Oracle's exception handing. IDK of a good solution. - OldProgrammer

2 Answers

3
votes

PL/SQL will only execute one exception block. So if you have some generic code (e.g. logging) you want to run for every exception, you have to it do in when others.

Then check the sqlcode to do exception specific handling. For example:

begin

  raise TOO_MANY_ROWS;

exception
  when others then
    dbms_output.put_line ( 'Generic stuff' );
    case sqlcode 
      when -1422 then
        dbms_output.put_line ( 'TMR specific' );
      when -1476 then
        dbms_output.put_line ( 'ZD specific' );
      else
        null;
    end case;

    raise;
end;
/

Generic stuff
TMR specific

ORA-01422: exact fetch returns more than requested number of rows

begin

  raise ZERO_DIVIDE;

exception
  when others then
    dbms_output.put_line ( 'Generic stuff' );
    case sqlcode 
      when -1422 then
        dbms_output.put_line ( 'TMR specific' );
      when -1476 then
        dbms_output.put_line ( 'ZD specific' );
      else
        null;
    end case;

    raise;
end;
/

Generic stuff
ZD specific

ORA-01476: divisor is equal to zero

Now, whether this is a good idea is debatable.

when others exception blocks should re-raise the exception in some way. This to prevent you from suppressing serious unexpected errors (like out of disk space). But for some specific exceptions you may want to carry on processing.

Addendum

If you have user-defined exceptions, you need to do a bit of extra work if you wand to handle these. One way to do it is to create a standard exceptions package. In it, name, initialize, and define value constants for all the exceptions you'll use.

You can then use these definitions for user-defined exceptions:

create or replace package excepts as 

  user_defined exception ;
  user_defined_val pls_integer := -20001;
  pragma exception_init ( user_defined, -20001 );

end;
/

begin

  raise excepts.USER_DEFINED;

exception
  when others then
    dbms_output.put_line ( 'Generic stuff' );
    case sqlcode 
      when -1422 then
        dbms_output.put_line ( 'TMR specific' );
      when -1476 then
        dbms_output.put_line ( 'ZD specific' );
      when excepts.user_defined_val then
        dbms_output.put_line ( 'User-defined specific' );
      else
        null;
    end case;

    raise;
end;
/

Generic stuff
User-defined specific

ORA-20001:
0
votes

If I'am undestanding correctly - solution is very simple. When you raising an exception, you can handle it in nested block and pass to the outer block by RAISE

    DECLARE
    test_exception EXCEPTION;
begin
      RAISE test_exception;

    --some code
EXCEPTION        
    WHEN OTHERS THEN
      BEGIN
         dbms_output.put_line( 'WRITE_TO_LOG');
         RAISE;
      EXCEPTION
        WHEN test_exception THEN
          dbms_output.put_line( 'test_exception');
         WHEN OTHERS THEN
        dbms_output.put_line( 'some other function');
     end;
END;

output:

WRITE_TO_LOG
send mail