【Oracle】PRAGMA AUTONOMOUS_TRANSACTION完全ガイド｜独立トランザクションで監査ログ・エラーログを確実に残す方法まで解説

-- プロシージャの宣言部に記述する（IS の直後）
CREATE OR REPLACE PROCEDURE write_audit_log(
    p_table_name  IN VARCHAR2,
    p_action      IN VARCHAR2,
    p_user_name   IN VARCHAR2,
    p_record_id   IN NUMBER
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;   -- ← 独立トランザクションとして動作させる
BEGIN
    INSERT INTO audit_log(
        log_id, table_name, action, changed_by, record_id, changed_at
    ) VALUES (
        audit_log_seq.NEXTVAL, p_table_name, p_action, p_user_name, p_record_id, SYSTIMESTAMP
    );

    COMMIT;   -- 独立トランザクション内で必ず COMMIT する
              -- 省略すると呼び出し元に戻った時点で ORA-06519 が発生する
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;   -- エラー時は ROLLBACK する（ORA-06519 防止）
        -- ログ書き込み失敗は握りつぶす（ログ失敗で本処理を妨げない）
END write_audit_log;
/

CREATE TABLE error_log (
    log_id       NUMBER         GENERATED ALWAYS AS IDENTITY,
    proc_name    VARCHAR2(100)  NOT NULL,
    error_code   NUMBER,
    error_msg    VARCHAR2(4000),
    error_stack  VARCHAR2(4000),
    created_at   TIMESTAMP      DEFAULT SYSTIMESTAMP,
    CONSTRAINT error_log_pk PRIMARY KEY (log_id)
);

CREATE OR REPLACE PROCEDURE log_error(
    p_proc_name  IN VARCHAR2,
    p_error_code IN NUMBER   DEFAULT SQLCODE,
    p_error_msg  IN VARCHAR2 DEFAULT SQLERRM
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO error_log(proc_name, error_code, error_msg, error_stack)
    VALUES (
        p_proc_name,
        p_error_code,
        p_error_msg,
        SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK
               || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 4000)
    );
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        NULL;   -- ログ失敗は無視（本処理への影響を避ける）
END log_error;
/

CREATE OR REPLACE PROCEDURE process_order(p_order_id IN NUMBER) AS
BEGIN
    -- メインの業務処理
    UPDATE orders SET status = 'PROCESSING' WHERE order_id = p_order_id;
    -- ... 他の処理 ...
    COMMIT;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- エラーログを書き込む（メインのROLLBACKとは独立してコミットされる）
        log_error(
            p_proc_name  => 'process_order',
            p_error_code => SQLCODE,
            p_error_msg  => 'order_id=' || p_order_id || ' が見つかりません'
        );
        ROLLBACK;   -- メインのトランザクションはロールバック
        RAISE;      -- 例外を再 RAISE する

    WHEN OTHERS THEN
        log_error(
            p_proc_name  => 'process_order',
            p_error_code => SQLCODE,
            p_error_msg  => SQLERRM
        );
        ROLLBACK;
        RAISE;
END process_order;
/

-- 実行例
BEGIN
    process_order(99999);   -- 存在しない order_id → ROLLBACK されるが error_log には記録される
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/

-- メインのロールバック後もエラーログは残っている
SELECT log_id, proc_name, error_code, error_msg, created_at
FROM error_log ORDER BY log_id DESC FETCH FIRST 5 ROWS ONLY;

-- 変更履歴テーブル
CREATE TABLE employees_change_log (
    change_id    NUMBER GENERATED ALWAYS AS IDENTITY,
    emp_id       NUMBER,
    action_type  VARCHAR2(10),   -- INSERT / UPDATE / DELETE
    old_salary   NUMBER,
    new_salary   NUMBER,
    changed_by   VARCHAR2(100),
    changed_at   TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT emp_change_log_pk PRIMARY KEY (change_id)
);

-- 独立トランザクションを使った変更履歴トリガー
CREATE OR REPLACE TRIGGER trg_employees_audit
    AFTER INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_action VARCHAR2(10);
BEGIN
    IF INSERTING THEN
        v_action := 'INSERT';
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
    ELSE
        v_action := 'DELETE';
    END IF;

    INSERT INTO employees_change_log(emp_id, action_type, old_salary, new_salary, changed_by)
    VALUES (
        COALESCE(:NEW.employee_id, :OLD.employee_id),
        v_action,
        :OLD.salary,
        :NEW.salary,
        SYS_CONTEXT('USERENV', 'SESSION_USER')
    );

    COMMIT;   -- トリガー内でも COMMIT が必要
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END trg_employees_audit;
/

-- パッケージ仕様部（PRAGMA は書かない）
CREATE OR REPLACE PACKAGE audit_pkg AS
    PROCEDURE log_dml(
        p_table_name IN VARCHAR2,
        p_action     IN VARCHAR2,
        p_row_count  IN NUMBER DEFAULT NULL
    );
END audit_pkg;
/

-- パッケージボディ（ここに PRAGMA を書く）
CREATE OR REPLACE PACKAGE BODY audit_pkg AS
    PROCEDURE log_dml(
        p_table_name IN VARCHAR2,
        p_action     IN VARCHAR2,
        p_row_count  IN NUMBER DEFAULT NULL
    ) AS
        PRAGMA AUTONOMOUS_TRANSACTION;   -- ボディ側の宣言部に記述
    BEGIN
        INSERT INTO audit_log(table_name, action, row_count, logged_by, logged_at)
        VALUES (p_table_name, p_action, p_row_count,
                SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSTIMESTAMP);
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN ROLLBACK;
    END log_dml;
END audit_pkg;
/

-- メインセッション
BEGIN
    INSERT INTO test_table(id, val) VALUES (1, 'test');
    -- ← まだ COMMIT していない

    -- 独立トランザクションから見ると、上の INSERT はまだ見えない
    check_proc();   -- PRAGMA AUTONOMOUS_TRANSACTION で宣言されたプロシージャ
    -- check_proc 内で SELECT * FROM test_table WHERE id=1 → 0件になる

    COMMIT;
END;
/
-- 独立トランザクションは「コミット済みのデータのみ」を参照する
-- メインセッションの未コミット変更は独立トランザクションから見えない
-- これによりデータ不整合が起きないよう設計する必要がある

-- Oracle では自律型トランザクションを最大 4 レベルまでネストできる
-- 5 レベル目以降は ORA-06519 ではなく ORA-04026 が発生する可能性がある
-- セッションあたりの独立トランザクション数の上限は SESSIONS パラメータに依存する

-- パラメータ確認
SELECT name, value FROM V$PARAMETER
WHERE name IN ('sessions', 'transactions');
-- TRANSACTIONS = SESSIONS × 1.1 が目安