【PL/SQL】FORALLとSAVE EXCEPTIONSでバルクDMLのエラーを個別処理する方法

大量データを高速に処理する際、PL/SQLのFORALLはDMLをまとめて送ることでコンテキストスイッチを削減できる強力な構文です。ただしバッチ内の一部行だけが制約違反で失敗すると、通常は全体が例外で中断されてしまいます。SAVE EXCEPTIONS句を併用すると、失敗行を個別に捕捉しながら他の行は継続実行できるため、エラーの多い現場データでも堅牢に取り込みが可能です。ここでは基本構文から、エラー内容の取り出し、入力行との突合、LIMITを使った段階的バルク処理、INSERT/UPDATE/MERGEそれぞれの使い所、運用の勘所までを整理します。

基本構文と例外収集の流れ

FORALLは「配列インデックスの範囲」を指定して一括DMLを発行します。SAVE EXCEPTIONSを付与した場合、失敗した各行の情報はSQL%BULK_EXCEPTIONSに蓄積され、FORALL終了後に例外が発生します。したがって例外節でSQL%BULK_EXCEPTIONSを反復し、失敗行のインデックスとエラーコードを読み出して後処理します。

DECLARE
  TYPE t_emp IS TABLE OF employees%ROWTYPE;
  v_rows t_emp;
BEGIN
  SELECT * BULK COLLECT INTO v_rows
  FROM staging_employees;

  BEGIN
    FORALL i IN v_rows.FIRST .. v_rows.LAST SAVE EXCEPTIONS
      INSERT INTO employees(empno, ename, deptno, sal)
      VALUES (v_rows(i).empno, v_rows(i).ename, v_rows(i).deptno, v_rows(i).sal);
  EXCEPTION
    WHEN OTHERS THEN
      FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
          'idx='||SQL%BULK_EXCEPTIONS(j).error_index||
          ' code='||SQL%BULK_EXCEPTIONS(j).error_code);
      END LOOP;
      -- 必要に応じてコミット・ロールバックやエラーログへの書き込みを行う
  END;

  COMMIT; -- 正常に挿入できた行のみ確定
END;
/

SQL%BULK_EXCEPTIONS(j).error_index はFORALLで指定した配列のインデックス、error_code はOracleエラー番号を表します。ここから元データを逆引きできるため、該当行の再処理や通知が容易になります。

入力データとエラーの突合処理

エラー行の中身を特定するには、error_indexを使って元コレクションの要素へアクセスします。共通関数化してエラーログ表へ記録すると実務で扱いやすくなります。

DECLARE
  TYPE t_emp IS TABLE OF employees%ROWTYPE;
  v_rows t_emp;

  PROCEDURE log_err(p_idx PLS_INTEGER, p_code NUMBER, p_rows IN t_emp) IS
  BEGIN
    INSERT INTO load_error(empno, message, created_at)
    VALUES (p_rows(p_idx).empno,
            'ORA-'||TO_CHAR(ABS(p_code)),
            SYSTIMESTAMP);
  END;
BEGIN
  SELECT * BULK COLLECT INTO v_rows FROM staging_employees;

  BEGIN
    FORALL i IN INDICES OF v_rows SAVE EXCEPTIONS
      INSERT INTO employees(empno, ename, deptno, sal)
      VALUES (v_rows(i).empno, v_rows(i).ename, v_rows(i).deptno, v_rows(i).sal);
  EXCEPTION
    WHEN OTHERS THEN
      FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
        log_err(SQL%BULK_EXCEPTIONS(j).error_index,
                SQL%BULK_EXCEPTIONS(j).error_code,
                v_rows);
      END LOOP;
  END;

  COMMIT;
END;
/

INDICES OF 句を使うとスパースなコレクションでも有効なインデックスのみを対象にできます。ロード中にフィルターを掛けて要素を削除した場合でも安全に扱えるのが利点です。

LIMITとFETCHでメモリを抑えつつ段階実行する

大量データではBULK COLLECTの取り込みでPGAを圧迫しないよう、LIMITで分割してループ処理します。各チャンクに対してFORALL … SAVE EXCEPTIONSを適用し、チャンクごとに例外収集とログ出力を行うと安定します。

DECLARE
  TYPE t_src IS TABLE OF staging_employees%ROWTYPE;
  v_rows t_src;
  c_limit CONSTANT PLS_INTEGER := 1000;
BEGIN
  LOOP
    v_rows := t_src(); -- 再初期化
    SELECT * BULK COLLECT INTO v_rows
    FROM staging_employees
    WHERE processed = 'N'
    FETCH FIRST c_limit ROWS ONLY;

    EXIT WHEN v_rows.COUNT = 0;

    BEGIN
      FORALL i IN 1 .. v_rows.COUNT SAVE EXCEPTIONS
        INSERT INTO employees(empno, ename, deptno, sal)
        VALUES (v_rows(i).empno, v_rows(i).ename, v_rows(i).deptno, v_rows(i).sal);
    EXCEPTION
      WHEN OTHERS THEN
        FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
          INSERT INTO load_error(empno, message, created_at)
          VALUES (v_rows(SQL%BULK_EXCEPTIONS(j).error_index).empno,
                  'ORA-'||TO_CHAR(ABS(SQL%BULK_EXCEPTIONS(j).error_code)),
                  SYSTIMESTAMP);
        END LOOP;
    END;

    UPDATE staging_employees
       SET processed = 'Y'
     WHERE empno IN (SELECT empno FROM TABLE(v_rows)); -- 簡略例
    COMMIT;
  END LOOP;
END;
/

コミット頻度は障害時のリカバリ時間とログ量のバランスで決めます。チャンク単位での確定が一般的ですが、厳密な整合性要件がある場合は一括確定も検討します。

UPDATEやMERGEでもSAVE EXCEPTIONSは有効

INSERTだけでなく、UPDATEやDELETE、MERGEにもFORALLは利用できます。UPSERT要件ではMERGEの集合志向アプローチが第一候補ですが、条件が極端に複雑で行単位の前処理が必要なときはFORALLで個別DMLを並列的に投げる設計も現実的です。

-- UPDATEの一括適用
FORALL i IN 1 .. v_rows.COUNT SAVE EXCEPTIONS
  UPDATE employees
     SET sal = v_rows(i).sal
   WHERE empno = v_rows(i).empno;

-- MERGEの一括適用（値束ね用にレコード型を使う例）
FORALL i IN 1 .. v_rows.COUNT SAVE EXCEPTIONS
  MERGE INTO employees d
  USING (SELECT v_rows(i).empno empno, v_rows(i).sal sal FROM dual) s
     ON (d.empno = s.empno)
   WHEN MATCHED THEN UPDATE SET d.sal = s.sal
   WHEN NOT MATCHED THEN INSERT (empno, sal) VALUES(s.empno, s.sal);

MERGEは1文で済む一方、SAVE EXCEPTIONSのエラー行特定はやや読みにくくなります。error_indexから元コレクションを必ず逆引きできるよう、値束ねの規約を統一しておくと分析が楽になります。

RETURNINGとバルクの組み合わせ

INSERTやUPDATEで生成されたキーや影響行の列値を回収したい場合、RETURNING BULK COLLECT INTO でまとめて受け取れます。SAVE EXCEPTIONSと併用する際は、成功行の戻り値のみが詰まる点を踏まえ、元配列のインデックス対応を別管理にするのが安全です。

DECLARE
  TYPE t_ids IS TABLE OF employees.empno%TYPE;
  v_ids t_ids;
BEGIN
  FORALL i IN 1 .. v_rows.COUNT SAVE EXCEPTIONS
    INSERT INTO employees(empno, ename) VALUES (v_rows(i).empno, v_rows(i).ename)
    RETURNING empno BULK COLLECT INTO v_ids;

  -- v_ids には成功分だけが入る。エラー分はSQL%BULK_EXCEPTIONSを参照
END;
/

よくある落とし穴と設計の勘所

SAVE EXCEPTIONSを付けるとFORALL終了時に一度だけ例外が上がるため、その外側で必ずWHEN OTHERSなどの例外節を用意し、SQL%BULK_EXCEPTIONSを反復して処理する必要があります。例外を握り潰してコミットしてしまうと、失敗行が行方不明になります。エラーコードはNEGATIVEな値で返るため、表示や比較の際はABSを取ると見やすく、標準化したメッセージ化ルールを用意しておくと運用が安定します。バインド対象のコレクションは列ごとに分ける方法とレコード型でまとめる方法があり、可読性と変更容易性の観点ではレコード型が有利です。PGA圧迫を避けるには不要列を持ち込まないこと、LOBや巨大な文字列は必要に応じて分割処理に切り出すことが重要です。

まとめ

FORALLは集合志向のDMLをPL/SQLから安全に高速化する仕組みで、SAVE EXCEPTIONSと組み合わせることで「成功は確定しつつ失敗は個別に扱う」堅牢なパターンを実現できます。error_indexで元データを特定し、LIMIT付きの段階処理とエラーログ記録をテンプレート化すれば、現場データ特有の品質ばらつきにも耐える取り込み基盤が構築できます。INSERT、UPDATE、MERGEのいずれでも活用できるため、要件に応じて最小のデータ移送回数と最大の健全性を両立させる設計を目指しましょう。