システム・トリガーを試す（４）（Oracle Database 19c)

　データベース上でエラーが発生したときにPL/SQLプログラムを実行したい場合にはAFTER SERVERERRORトリガーを作成します。利用用途としては外部システムへのエラー通知や独自のログにエラーを記録する等が考えられます。
AFTER SERVERERRORトリガーは以下の両方の条件がそろった場合に発行されます（マニュアルより）

• サーバー・エラー・メッセージがログに記録される。
• Oracleリレーショナル・データベース管理システム(RDBMS)が、エラー・トリガーを起動することが妥当であると判断する。

　インスタンス起動処理中や、クリティカル・エラー発生時にはトリガーは起動しません。マニュアルの英文「RDBMS is starting up.」を日本語では「RDBMSが起動中である。」としているのは良い訳とはいえません。

AFTER SERVERERRORトリガー

　AFTER SERVERERRORトリガーを作成するにはCREATE TRIGGER文にAFTER SERVERERROR 句を指定します。下記の例は発生したエラーの情報を取得し、SQL文と共にアラートログに書き込んでいます。

CREATE OR REPLACE TRIGGER servererror_trigger1 AFTER SERVERERROR ON DATABASE
DECLARE
    evt VARCHAR2(128);
    own VARCHAR2(128);
    nam VARCHAR2(128);
    typ VARCHAR2(128);
    err NUMBER;
    num NUMBER;
    txt ora_name_list_t;
    str clob;
    msg VARCHAR2(4000);
BEGIN
    num := ora_sql_txt(txt);
    FOR i IN 1 .. num LOOP
        str := str || txt(i);
    END LOOP;

    evt := ora_sysevent;
    own := ora_dict_obj_owner;
    nam := ora_dict_obj_name;
    err := ora_server_error(1);
    msg := ora_server_error_msg(1);

    DBMS_SYSTEM.KSDWRT(2, TO_CHAR(SYSTIMESTAMP) || ': AFTER SERVERERROR TRIGGER1: Event: ' || evt || ' Name: ' || nam || ', SQL: ' || str);
    DBMS_SYSTEM.KSDWRT(2, TO_CHAR(SYSTIMESTAMP) || ': AFTER SERVERERROR TRIGGER2: Error: ' || msg  || ' Code: ' || to_char(err));
END;
/

WHEN句の指定

特定のエラーが発生した時だけ実行する方法は、マニュアル に以下の記述があります。

この句をSERVERERRORトリガーに指定する場合は、conditionをERRNO = error_codeにする必要があります。

しかしこの構文は実際に実行してみると以下のようなエラーになるためマニュアルの記述間違いだと思われます（Oracle Database 11gのころから変わりません）。

SQL> CREATE OR REPLACE TRIGGER servererror_trigger1 AFTER SERVERERROR ON DATABASE
  2  WHEN (ERRNO = 1542)
  3  BEGIN
  4      NULL;
  5  END;
  6  /
WHEN (ERRNO = 1542)
      *
行2でエラーが発生しました。:
ORA-00904: "ERRNO": 無効な識別子です。

エラー情報の把握

　トリガー内でora_server_error関数とora_server_error_msg関数を呼ぶことでエラー情報を取得することができます。先頭のエラーを取得するにはパラメーターに「1」を指定します。エラーの個数はora_server_error_depthで知ることができます。

　SQL文の実行エラーの場合は、ora_sql_txt関数で文字列の配列が取得できます。上記の例ではFOR LOOPでCLOB型の文字列に追記しています。下記は表領域作成エラーが発生した時のエラー・メッセージとアラートログに書き込まれた情報です。表領域が作成できていないので、エラーになった表領域名は「ora_dict_obj_name」関数で取得できていません。

SQL> CREATE TABLESPACE ts1 DATAFILE '/tmp/ts1.dbf' SIZE 1T;
CREATE TABLESPACE ts1 DATAFILE '/tmp/ts1.dbf' SIZE 1T
*
行1でエラーが発生しました。:
ORA-01144:
ファイル・サイズ(134217728ブロック)が最大値4194303ブロックを超えています。

出力されたアラートログ

21-04-14 17:22:11.140347000 +09:00: AFTER SERVERERROR TRIGGER1: Event: SERVERERROR Name: , SQL: CREATE TABLESPACE ts1 DATAFILE '/tmp/ts1.dbf' SIZE 1T
21-04-14 17:22:11.140450000 +09:00: AFTER SERVERERROR TRIGGER2: Error: ORA-01144: ファイル・サイズ(134217728ブロック)が最大値4194303ブロックを超えています。
 Code: 1144

アラートログへの書き込み

アラートログへの書き込みはDBMS_SYSTEMパッケージを使っています。このパッケージはマニュアルには記載されていませんが、古いバージョンのOracle Databaseから存在が知られています。このパッケージは${ORACLE_HOME}/rdbms/admin/prvtsys.plbファイルで定義されています。

KSDWRTプロシージャは２つのパラメーターを取り、最初のパラメータは出力場所、２つ目のパラメーターは出力文字列です。

出力場所のパラメータ値	説明
1	アラートログに出力
2	トレースログに出力
3	両方に出力

SQL> EXECUTE DBMS_SYSTEM.KSDWRT(3, 'Output Test1');

PL/SQLプロシージャが正常に完了しました。

SQL> EXIT
$
$ cd ${ORACLE_BASE}/diag/rdbms/o19a/O19A/trace
$ grep 'Output Test1' *.trc
O19A_ora_84036.trc:Output Test1
$ grep 'Output Test1' alert_O19A.log
Output Test1