oracle - 何时在 Oracle 中重新引发相同的异常

Question

我正在阅读 Steven Feuerstein 的 PL/SQL 一书。根据它:

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.

我试一试:

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;

我的 log_error 表中有以下数据:

-1476 ORA-01476: divisor is equal to zero

现在我将 raise 放在我的异常 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;

现在，当我运行这个 block 时，我的日志表中什么也没有，而且我也收到了错误。

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

加薪有什么用？我什么时候必须使用这个？

Answer 1

您的日志记录过程需要稍有不同。当调用异常时，所有未提交的数据/事务都将回滚。您可以使用 Oracle 添加的新功能添加更多详细信息。

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

您需要创建一个使用 PRAGMA AUTONOMOUS TRANSACTION 的日志程序。传入带有详细信息的 SQLCODE 和 err_msg，无论如何都会记录错误。这是我使用的，它也使用了 Feurstein 的 Q Error 包。 This link Q$Error 包的信息非常丰富。

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;