When to re-raise the same exception in Oracle

Question

I'm reading Steven Feuerstein's PL/SQL book. According to it:

Use this form when you want to re-raise (or propagate out) the same exception from within an exception handler, as you see here:

EXCEPTION 
 WHEN NO_DATA_FOUND 
 THEN 
 -- Use common package to record all the "context" information,
 -- such as error code, program name, etc. 
 errlog.putline (company_id_in); 
 -- And now propagate NO_DATA_FOUND unhandled to the enclosing block.
 RAISE;

This feature is useful when you want to log the fact that an error occurred, but then pass that same error out to the enclosing block. That way, you record where the error occurred in your application but still stop the enclosing block(s) without losing the error information.

I give it a try:

create table log_error
    ( 
      error_code number,
      error_name varchar2(400)
    );


 declare
    l_q number := 400;
    l_r number := 0;
    l_result number;
    err_num NUMBER;
    err_msg VARCHAR2(100);
begin
    l_result := l_q/l_r; 
 EXCEPTION
   WHEN OTHERS THEN
  err_num := SQLCODE;
  err_msg := SUBSTR(SQLERRM, 1, 100);
  insert into log_error values (err_num , err_msg);     
end;

select * from log_error;

I got below data in my log_error table:

-1476 ORA-01476: divisor is equal to zero

Now I place raise in my exception block:

    declare
    l_q number := 400;
    l_r number := 0;
    l_result number;
    err_num NUMBER;
    err_msg VARCHAR2(100);
    begin
    l_result := l_q/l_r; 
    EXCEPTION
      WHEN OTHERS THEN
        err_num := SQLCODE;
        err_msg := SUBSTR(SQLERRM, 1, 100);
        insert into log_error values (err_num , err_msg);     
        raise;
   end;

Now when I run this block, i got nothing in my log table and also I got the error.

Error report -
ORA-01476: divisor is equal to zero
ORA-06512: at line 14
01476. 00000 -  "divisor is equal to zero"

What is the use of raise? When I have to use this?

kevinskio · Accepted Answer

Your logging process needs to be a little different. When an exception is called all data/transactions that are not committed are rolled back. You can add more details with the newer features Oracle has added.

err_msg := DBMS_UTILITY.FORMAT_ERROR_STACK()||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE();

You need to create a logging procedure which uses the PRAGMA AUTONOMOUS TRANSACTION. Pass in the SQLCODE and err_msg with the details and this will log the error no matter what. Here is what I use which also uses Feurstein's Q Error package. This link to the Q$Error package is quite informative.

PROCEDURE LOG (err_in         IN INTEGER:= SQLCODE,
                  msg_in         IN VARCHAR2:= NULL,
                  vlocation_in   IN VARCHAR2:= NULL)
   IS
      /******************************************************************************
      PURPOSE: log a code error, business logic error or information message in APPLICATION_ERROR_LOGGING
      we want the error message to be logged even if the calling transaction fails or hangs
      ******************************************************************************/

      PRAGMA AUTONOMOUS_TRANSACTION;
      v_err_text   VARCHAR2 (4000) := SQLERRM;
   BEGIN
      v_err_text := v_err_text || ' ' || GET_MORE_ERROR_DESCRIPTION (err_in);

      INSERT INTO application_error_logging (ID,
                                                     request_uri,
                                                     ERROR_CODE,
                                                     user_id,
                                                     stack_trace,
                                                     information,
                                                     "TIMESTAMP")
        VALUES   (application_error_logging_seq.NEXTVAL,
                  vlocation_in,
                  TO_CHAR (err_in),
                  g_admin_id,
                  msg_in,
                  v_err_text,
                  localtimestamp);

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         q$error_manager.raise_error (
            error_code_in   => SQLCODE,
            text_in         => SQLERRM,
            name1_in        => 'LOCATION',
            value1_in       => 'APP_UTIL.LOG',
            name2_in        => 'v_location',
            value2_in       => vlocation_in,
            name3_in        => 'err_in',
            value3_in       => TO_CHAR (err_in)
         );
   END LOG;

When to re-raise the same exception in Oracle

Answers (2)

Related Questions