【PL/SQL】FORALL SAVE EXCEPTIONSで失敗行をログ化 | コーディングライフスタイル

FORALL SAVE EXCEPTIONS は、PL/SQLで大量DMLを高速化しながら、失敗した行だけを後から個別に処理するための構文です。FORALL は1行ずつSQLを発行するループよりコンテキストスイッチを減らせますが、通常は途中の1件でエラーになると処理が止まります。SAVE EXCEPTIONS を付けると、失敗を記録しながら残りのDMLを続け、最後に ORA-24381 を発生させます。

この記事では、FORALL全体の概要ではなく、失敗行をログ表へ残す実務テンプレートに絞って解説します。BULK COLLECT / FORALL全体の基礎は BULK COLLECT / FORALL完全ガイド、PL/SQLバルク処理全体の設計は PL/SQLバルク処理完全ガイドも参考にしてください。

この記事で扱うこと

SAVE EXCEPTIONS と ORA-24381 の基本
SQL%BULK_EXCEPTIONS の処理方法
ERROR_INDEX から元データを特定する考え方
失敗行をログ表へ保存するテンプレート
LIMIT 分割時のエラー処理
DBMS_ERRLOG との使い分け
失敗を握りつぶさないための設計

SAVE EXCEPTIONSで何が変わるか
ORA-24381を明示的に扱う
基本形: 失敗行を表示する
ERROR_INDEXは元配列を特定する鍵
失敗行ログ表を作る
実務テンプレート: UPDATE失敗行をログ化する
失敗があったら成功扱いにしない
LIMIT分割でメモリを抑える
INDICES OFを使う場合の注意
INSERTやMERGEでも使える
DBMS_ERRLOGとの使い分け
RETURNINGとの組み合わせ
例外を再送出するか判断する
よくある落とし穴
よくある質問
まとめ

SAVE EXCEPTIONSで何が変わるか

SAVE EXCEPTIONS なしの FORALL では、途中のDMLでエラーが発生するとそこで例外になり、後続の要素は処理されません。SAVE EXCEPTIONS を付けると、失敗した要素を内部的に記録しながら、可能な限り後続のDMLを続けます。そしてFORALLの完了後に、保存された例外があることを示す ORA-24381 が発生します。

高速化FORALLにより、PL/SQLとSQLエンジン間の呼び出し回数を減らせます。

継続処理SAVE EXCEPTIONSにより、1件の失敗で全体を止めず、他の行を処理できます。

後処理必須失敗行は SQL%BULK_EXCEPTIONS を読んで必ず処理します。

成功扱いにしない失敗があるのに握りつぶすと、データ欠損に気づけなくなります。

ORA-24381を明示的に扱う

SAVE EXCEPTIONS で保存されたエラーがある場合、FORALL完了後に ORA-24381 が発生します。PL/SQLでは、例外名を定義して PRAGMA EXCEPTION_INIT で紐付けておくと、例外ハンドラを読みやすくできます。

define-bulk-errors-exception.sql

DECLARE
  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  NULL;
EXCEPTION
  WHEN e_bulk_errors THEN
    DBMS_OUTPUT.PUT_LINE('FORALLで一部行が失敗しました');
END;
/

大事なのは、ORA-24381 を捕まえた後に SQL%BULK_EXCEPTIONS を必ず読むことです。例外だけ捕まえて何もしないコードは、失敗行を見失う危険な実装です。

基本形: 失敗行を表示する

まずは最小構成です。FORALL ... SAVE EXCEPTIONS の例外ハンドラで、SQL%BULK_EXCEPTIONS.COUNT、ERROR_INDEX、ERROR_CODE を確認します。

forall-save-exceptions-basic.sql

DECLARE
  TYPE t_id_tab IS TABLE OF employees.employee_id%TYPE;
  TYPE t_sal_tab IS TABLE OF employees.salary%TYPE;

  l_ids      t_id_tab := t_id_tab(101, 102, 103);
  l_salaries t_sal_tab := t_sal_tab(5000, -1, 7000);

  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  FORALL i IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
    UPDATE employees
    SET salary = l_salaries(i)
    WHERE employee_id = l_ids(i);

EXCEPTION
  WHEN e_bulk_errors THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        'index=' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
        ', code=' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE ||
        ', id=' || l_ids(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX)
      );
    END LOOP;
END;
/

ERROR_CODE はOracleエラー番号の数値部分です。エラーメッセージへ変換する場合は、SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE) のように負数で渡します。

ERROR_INDEXは元配列を特定する鍵

ERROR_INDEX は、FORALLで失敗した反復の添字です。単純な FORALL i IN 1 .. l_ids.COUNT なら、l_ids(ERROR_INDEX) で元データを引けます。失敗行をログ化するには、この添字から業務キー、入力値、処理対象データを取り出します。

bulk-exception-error-index.sql

FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
  l_error_index := SQL%BULK_EXCEPTIONS(j).ERROR_INDEX;
  l_error_code  := SQL%BULK_EXCEPTIONS(j).ERROR_CODE;

  INSERT INTO bulk_error_log (
    source_key,
    error_code,
    error_message,
    input_value
  ) VALUES (
    l_ids(l_error_index),
    l_error_code,
    SQLERRM(-l_error_code),
    l_salaries(l_error_index)
  );
END LOOP;

注意: INDICES OF や VALUES OF を使う場合、ERROR_INDEXの読み方が単純な連番とは変わります。まずは連番配列で設計し、複雑な添字を使う場合はエラー時に元キーを確実に引ける構造にしてください。

失敗行ログ表を作る

本番処理では DBMS_OUTPUT ではなく、失敗行をログ表へ残します。ログには、バッチID、処理名、元データのキー、配列添字、エラーコード、エラーメッセージ、入力値を残すと調査しやすくなります。

create-bulk-error-log.sql

CREATE TABLE bulk_dml_error_log (
  log_id        NUMBER GENERATED BY DEFAULT AS IDENTITY,
  batch_id      NUMBER,
  process_name  VARCHAR2(100),
  source_key    VARCHAR2(200),
  error_index   NUMBER,
  error_code    NUMBER,
  error_message VARCHAR2(4000),
  input_payload  VARCHAR2(4000),
  created_at    TIMESTAMP DEFAULT SYSTIMESTAMP,
  CONSTRAINT pk_bulk_dml_error_log PRIMARY KEY (log_id)
);

入力値が複数列ある場合は、JSON文字列や区切り文字列として input_payload に残すか、業務ごとの専用ログ表を作ります。あとから再実行する可能性があるなら、再実行に必要なキーを必ず残します。

実務テンプレート: UPDATE失敗行をログ化する

次は、従業員の給与更新を例にした実務テンプレートです。失敗行をログ化し、成功行まで巻き戻すかどうかは業務要件に合わせて判断します。

forall-update-with-error-log.sql

DECLARE
  TYPE t_emp_id_tab IS TABLE OF employees.employee_id%TYPE;
  TYPE t_salary_tab IS TABLE OF employees.salary%TYPE;

  l_emp_ids  t_emp_id_tab;
  l_salaries t_salary_tab;
  l_batch_id NUMBER := 2026052601;

  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  SELECT employee_id, new_salary
  BULK COLLECT INTO l_emp_ids, l_salaries
  FROM salary_update_work
  WHERE batch_id = l_batch_id
    AND status = 'READY';

  BEGIN
    FORALL i IN 1 .. l_emp_ids.COUNT SAVE EXCEPTIONS
      UPDATE employees
      SET salary = l_salaries(i)
      WHERE employee_id = l_emp_ids(i);

  EXCEPTION
    WHEN e_bulk_errors THEN
      FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
        INSERT INTO bulk_dml_error_log (
          batch_id,
          process_name,
          source_key,
          error_index,
          error_code,
          error_message,
          input_payload
        ) VALUES (
          l_batch_id,
          'SALARY_UPDATE',
          TO_CHAR(l_emp_ids(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX)),
          SQL%BULK_EXCEPTIONS(j).ERROR_INDEX,
          SQL%BULK_EXCEPTIONS(j).ERROR_CODE,
          SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE),
          'salary=' || l_salaries(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX)
        );
      END LOOP;
  END;

  COMMIT;
END;
/

このテンプレートでは失敗行をログ化したうえで成功行をコミットしています。全件成功が必須なら、ログだけ残して ROLLBACK する、または例外を再送出する設計にします。

失敗があったら成功扱いにしない

SAVE EXCEPTIONS を使うと、成功した行と失敗した行が混在します。そのため、ログ化しただけで処理ステータスを DONE にしてしまうと、失敗行が放置されます。少なくとも、バッチ全体の状態を PARTIAL_ERROR や ERROR に分けて記録します。

mark-batch-partial-error.sql

IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
  UPDATE batch_control
  SET status = 'PARTIAL_ERROR',
      error_count = SQL%BULK_EXCEPTIONS.COUNT,
      finished_at = SYSTIMESTAMP
  WHERE batch_id = l_batch_id;
ELSE
  UPDATE batch_control
  SET status = 'DONE',
      finished_at = SYSTIMESTAMP
  WHERE batch_id = l_batch_id;
END IF;

ジョブ実行履歴や異常検知を合わせて設計するなら、ジョブ実行履歴の集中管理と異常検知のような管理テーブルも参考になります。

LIMIT分割でメモリを抑える

大量データを一度に BULK COLLECT すると、PGAメモリを大きく使います。LIMIT で分割取得し、チャンクごとに FORALL SAVE EXCEPTIONS を実行すると、メモリ使用量を抑えられます。カーソルやBULK COLLECTの基礎は Oracle明示的カーソル完全ガイドも参考になります。

bulk-collect-limit-save-exceptions.sql

DECLARE
  CURSOR c_work IS
    SELECT work_id, employee_id, new_salary
    FROM salary_update_work
    WHERE status = 'READY'
    ORDER BY work_id;

  TYPE t_work_id_tab IS TABLE OF salary_update_work.work_id%TYPE;
  TYPE t_emp_id_tab  IS TABLE OF salary_update_work.employee_id%TYPE;
  TYPE t_salary_tab  IS TABLE OF salary_update_work.new_salary%TYPE;

  l_work_ids  t_work_id_tab;
  l_emp_ids   t_emp_id_tab;
  l_salaries  t_salary_tab;

  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  OPEN c_work;
  LOOP
    FETCH c_work BULK COLLECT INTO l_work_ids, l_emp_ids, l_salaries LIMIT 1000;
    EXIT WHEN l_work_ids.COUNT = 0;

    BEGIN
      FORALL i IN 1 .. l_work_ids.COUNT SAVE EXCEPTIONS
        UPDATE employees
        SET salary = l_salaries(i)
        WHERE employee_id = l_emp_ids(i);

    EXCEPTION
      WHEN e_bulk_errors THEN
        FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
          INSERT INTO bulk_dml_error_log(source_key, error_index, error_code, error_message)
          VALUES (
            TO_CHAR(l_work_ids(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX)),
            SQL%BULK_EXCEPTIONS(j).ERROR_INDEX,
            SQL%BULK_EXCEPTIONS(j).ERROR_CODE,
            SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)
          );
        END LOOP;
    END;

    COMMIT;
  END LOOP;
  CLOSE c_work;
END;
/

チャンクごとにCOMMITする場合、途中失敗時の再実行単位もチャンクになります。ワークテーブルにステータスを持たせ、処理済み・失敗・未処理を分けると再実行しやすくなります。PL/SQLの一括処理最適化は BULK COLLECTとFORALLによる一括処理パターンにもまとめています。

INDICES OFを使う場合の注意

疎な配列を扱う場合は FORALL i IN INDICES OF collection を使えます。ただし、エラー発生時に ERROR_INDEX から元データを特定できるよう、キー配列やログ用配列を必ず持っておきます。

forall-indices-of-warning.sql

-- 疎な配列を扱う例。ERROR_INDEXから元キーを引ける設計にする
FORALL i IN INDICES OF l_emp_ids SAVE EXCEPTIONS
  UPDATE employees
  SET salary = l_salaries(i)
  WHERE employee_id = l_emp_ids(i);

-- 例外ハンドラでは、ERROR_INDEXを使って同じ添字のキーを参照する
l_error_emp_id := l_emp_ids(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX);

VALUES OF を使う場合は、FORALLの反復が別のインデックス配列に基づきます。複雑な添字制御をするほどログ化が難しくなるため、業務キーを明示的に持ったレコード配列やワークテーブルを使う設計も検討します。

INSERTやMERGEでも使える

FORALL SAVE EXCEPTIONS はUPDATEだけでなく、INSERT、DELETE、MERGEでも利用できます。ただし、MERGEでは一意制約、更新対象の重複、ORA-30926などの業務エラーが起きやすいため、ログ化と再実行方針が重要です。MERGEの実務パターンは Oracle MERGE文完全ガイドや PL/SQL MERGE文の活用法を参考にしてください。

forall-insert-save-exceptions.sql

BEGIN
  FORALL i IN 1 .. l_emp_ids.COUNT SAVE EXCEPTIONS
    INSERT INTO employee_bonus(employee_id, bonus_amount)
    VALUES (l_emp_ids(i), l_bonus_amounts(i));
EXCEPTION
  WHEN e_bulk_errors THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      INSERT INTO bulk_dml_error_log(source_key, error_code, error_message)
      VALUES (
        TO_CHAR(l_emp_ids(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX)),
        SQL%BULK_EXCEPTIONS(j).ERROR_CODE,
        SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)
      );
    END LOOP;
END;

DBMS_ERRLOGとの使い分け

失敗行を残す方法には DBMS_ERRLOG もあります。DBMS_ERRLOG はSQLの LOG ERRORS 句でエラー行を自動収集する仕組みで、SQLだけで完結するINSERT/UPDATE/MERGEに向いています。一方、FORALL SAVE EXCEPTIONS はPL/SQL配列の元データと突き合わせて、独自のログや再実行制御をしたい場合に向いています。詳しくは DBMS_ERRLOGの使い方も参照してください。

FORALL SAVE EXCEPTIONSPL/SQL配列の入力値、業務キー、独自ステータスと紐付けてログ化したい場合に向きます。

DBMS_ERRLOGSQL単体で失敗行を自動的にエラーテーブルへ残したい場合に向きます。

全件成功必須失敗が1件でもあればROLLBACKし、ログだけ別トランザクションで残す設計を検討します。

部分成功許容成功行をCOMMITし、失敗行を再処理キューに回す設計ができます。

RETURNINGとの組み合わせ

FORALL では RETURNING BULK COLLECT INTO を使って、DML結果を配列に受け取ることもできます。ただし、SAVE EXCEPTIONS と組み合わせる場合、成功行と失敗行が混在するため、戻り値配列と入力配列の対応を慎重に扱います。RETURNINGを使う処理では、まずシンプルなケースで対応関係を検証してから本番実装に進めるのが安全です。動的SQLやRETURNINGの応用は Oracle EXECUTE IMMEDIATE完全ガイドも参考になります。

forall-returning-bulk-collect.sql

FORALL i IN 1 .. l_source_ids.COUNT SAVE EXCEPTIONS
  INSERT INTO target_table(source_id, value_text)
  VALUES (l_source_ids(i), l_values(i))
  RETURNING target_id BULK COLLECT INTO l_target_ids;

注意: RETURNING結果を業務キーと正確に紐付ける必要がある処理では、DML後にキーで再検索する、ワークテーブルを使う、成功行ログを別に残すなど、調査しやすい設計にしてください。

例外を再送出するか判断する

失敗行をログ化した後に処理を続けるか、全体を失敗として呼び出し元へ例外を返すかは業務要件で変わります。たとえば、100万件中10件の住所不正なら部分成功でよいかもしれません。一方、会計仕訳や在庫引当のように整合性が重要な処理では、1件でも失敗したら全体を失敗にするべき場合があります。

reraising-after-bulk-errors.sql

IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
  -- ログ化したあと、呼び出し元にも失敗を通知する
  RAISE_APPLICATION_ERROR(
    -20010,
    'bulk dml failed. error_count=' || SQL%BULK_EXCEPTIONS.COUNT
  );
END IF;

よくある落とし穴

例外を握りつぶすログ化せずにORA-24381だけ捕まえると、失敗行が消えます。

ERROR_INDEXを誤解するFORALL反復の添字です。元配列・業務キーと対応付けて処理します。

COMMIT単位が曖昧部分成功を許すのか、全件成功必須なのかを先に決めます。

LIMITが大きすぎる一度に集めすぎるとPGAを圧迫します。実測して調整します。

再実行できないログエラーコードだけでなく、再処理に必要な業務キーと入力値を残します。

よくある質問

Q. SAVE EXCEPTIONSを付ければ失敗しても成功扱いですか？

A. いいえ。失敗行がある場合は最後にORA-24381が発生します。例外ハンドラでSQL%BULK_EXCEPTIONSを処理してください。

Q. ERROR_INDEXはテーブルの行番号ですか？

A. 違います。FORALL反復の添字です。元配列や業務キーと対応付けて失敗行を特定します。

Q. DBMS_ERRLOGとどちらを使うべきですか？

A. SQLだけでエラー行を残したいならDBMS_ERRLOG、PL/SQL配列や独自ログと紐付けたいならFORALL SAVE EXCEPTIONSが向いています。

Q. 失敗行だけ再実行できますか？

A. できます。ログ表に業務キーと入力値を残しておけば、失敗行だけをワークテーブルへ戻して再処理できます。

Q. LIMITはいくつがよいですか？

A. 固定の正解はありません。100〜1000程度から始め、PGA使用量、処理時間、UNDO/REDO、コミット単位を見て調整します。

まとめ

FORALL SAVE EXCEPTIONS は、大量DMLを高速化しながら失敗行を個別に扱うための強力な仕組みです。ただし、SAVE EXCEPTIONS を付けただけでは不十分です。ORA-24381 を捕まえ、SQL%BULK_EXCEPTIONS の ERROR_INDEX と ERROR_CODE を読み、元配列や業務キーと突き合わせてログ化するところまでが実装です。

本番では、失敗行ログ表、バッチステータス、再実行方針、COMMIT/ROLLBACK単位をあらかじめ決めておきましょう。部分成功を許す処理では失敗行だけを再処理できるログを残し、全件成功が必須の処理ではログ化後に例外を再送出します。高速化と安全性の両方を満たすには、FORALLそのものよりも、失敗行をどう扱うかの設計が肝です。

参考: Oracle Database PL/SQL Language Reference – Bulk SQL and Bulk Binding