それPLSQLで出来るよ（その２ 簡単にエラーログを記録する。）

PLSQLでエラーが発生したけどメッセージがざっくりし過ぎてイマイチどこが原因かわからない時ってありませんか？

一応、PLSQLでもツールを利用すればトレースは可能ですが、面倒だったり、対象のPROCEDUREの呼び出し方がちょっと特殊だったり、使えないことが多々あります。

机上デバッグもいいですが、サクッとエラーログを記録するPROCEDUREを追加したほうが楽な場合もありますので、その紹介。

（と、言ってもDBMS_UTILITYの紹介みたいなものですが……）

準備

まずはエラー登録先のテーブルを作成します。

DEBUG_TBL.sql

CREATE TABLE DEBUG_TBL
(
    ROW_NO                         NUMBER(10,0) NOT NULL,
    INSERT_DATE                    DATE NOT NULL,
    SQLCODE                        VARCHAR2(4000),
    SQLERRM                        VARCHAR2(4000),
    FORMAT_ERROR_BACKTRACE         VARCHAR2(4000),
    FORMAT_CALL_STACK              VARCHAR2(4000)
)

次に、エラー記録用のPROCEDUREを作成。

DEBUG.sql

CREATE OR REPLACE PROCEDURE DEBUG
IS
  rec DEBUG_TBL%ROWTYPE;
BEGIN
  Select NVL(MAX(ROW_NO),0) + 1 Into rec.ROW_NO From DEBUG_TBL;
  rec.INSERT_DATE := SYSDATE;
  rec.SQLCODE  := SQLCODE;
  rec.SQLERRM  := SQLERRM;
  -- Oracle Database 10g Release 2 以降で利用可能
  rec.FORMAT_ERROR_BACKTRACE := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  rec.FORMAT_CALL_STACK      := DBMS_UTILITY.FORMAT_CALL_STACK;
  
  insert into DEBUG_TBL values rec;
  COMMIT;
END DEBUG;
/

以上です。

使い方

エラーログを記録したいPROCEDUREのExceptionに以下を記述します。

  EXCEPTION
    WHEN OTHERS THEN
      DEBUG;

確認

簡単な確認用のパッケージを用意します。

CREATE OR REPLACE PACKAGE BODY TEST
IS
  vNum  NUMBER(2);
  
  PROCEDURE testDebug02
  IS
  BEGIN
    vNum := 0/0;
  EXCEPTION
    WHEN OTHERS THEN　-- 行10
      NULL;
  END testDebug02;
  
  PROCEDURE testDebug01
  IS
  BEGIN
    vNum := 0/0;  -- 行17
  END testDebug01;
  
  PROCEDURE testDebug
  IS
  BEGIN
    testDebug02;  -- 行23
    testDebug01;  -- 行24
  EXCEPTION
    WHEN OTHERS THEN
      DEBUG;
  END testDebug;
END TEST;
/

実行後のDEBUG_TBLです。

select * from DEBUG_TBL

=======================

ROW_NO	
1

INSERT_DATE	
2020/01/15 11:01:56

SQLCODE	
-1476

SQLERRM	
ORA-01476: 除数がゼロです

FORMAT_ERROR_BACKTRACE	
ORA-06512: "HOGE.TEST", 行17
ORA-06512: "HOGE.TEST", 行24

FORMAT_CALL_STACK
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00007FFB7BED1090        10  procedure HOGE.DEBUG
00007FFBB53951F8        28  package body HOGE.TEST
00007FFBAB7B07A8         2  anonymous block

エラー行がきちんと記録されていますね。

注意点

当たり前ですが、呼び出し先のPROCEDUREでExceptionが握り潰されているとうまく拾えません。（行10）

以下のようにエラーが適切な範囲で拾われていることが前提となります。

  EXCEPTION
    WHEN ZERO_DIVIDE THEN　-- 0除算の場合は処理しない。
      NULL;
    WHEN OTHERS THEN　-- 上記以外はRAISEする。
      RAISE;

ダメゼッタイ　なんでもかんでも WHEN OTHER THEN