ORACLE PL/SQL : How to pass an exception into a procedure-CodePudding

Is there a way to pass exception into a procedure to call "raise" after doing some actions to process exception. So that the outer code block will get the exact exception that was raised

Something like this:

begin
    ...
exception
    when others then
        error_handler( err );
end;

procedure error_handler ( err ) is
begin
    /*
        here some code to handle and log the exception...
    */

    raise err;
end;

Or the only way is to pass SQLCODE and SQLERRM into the procedure and later call raise_application_error( SQLCODE, SQLERRM )?

CodePudding user response：

If I understood you correctly, this is what you're asking.

Sample log table:

SQL> create table err_log
  2    (program    varchar2(30),
  3     datum      date,
  4     sqlcode    number
  5    );

Table created.

Logging procedure should be an autonomous transaction so that you could commit in it, without affecting main transaction.

SQL> create or replace procedure error_handler
  2    (p_program in varchar2, p_sqlcode in number)
  3  is
  4    pragma autonomous_transaction;
  5  begin
  6    insert into err_log (program, datum, sqlcode)
  7      values (p_program, sysdate, p_sqlcode);
  8    commit;
  9  end;
 10  /

Procedure created.

Another procedure (whose execution you're logging); it'll raise division by zero. See lines #8 and #9 which call the logging procedure and then just re-raise the error:

SQL> create or replace procedure p_test is
  2    l_program varchar2(30) := 'P_TEST';
  3    l_value   number;
  4  begin
  5    l_value := 1 / 0;
  6  exception
  7    when others then
  8      error_handler(l_program, sqlcode);
  9      raise;
 10  end p_test;
 11  /

Procedure created.

OK, everything is set. Let's try it:

SQL> exec p_test;
BEGIN p_test; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero         --> this is result of RAISE in line #9
ORA-06512: at "SCOTT.P_TEST", line 9
ORA-06512: at "SCOTT.P_TEST", line 5
ORA-06512: at line 1

Log table contents:

SQL> select * from err_log;

PROGRAM                        DATUM                  SQLCODE
------------------------------ ------------------- ----------
P_TEST                         01.11.2022 11:13:31      -1476

SQL>

You asked, literally:

How to pass an exception into a procedure?

You can't, as far as I can tell:

SQL> create or replace procedure error_handler_text
  2    (p_err in exception)                  --> if this is what you asked
  3  is
  4  begin
  5    null;
  6  end;
  7  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE ERROR_HANDLER_TEXT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/13     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         out <an identifier> <a double-quoted delimited-identifier>
         table columns long double ref char standard time timestamp
         interval date binary national character nchar
         The symbol "<an identifier> was inserted before "EXCEPTION" to
         continue.

SQL>