【PL/SQL】DBMS_ERRLOGとLOG ERRORSの使い方｜REJECT LIMIT・ERR$_表・再処理設計まで

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG(
    dml_table_name      => 'SALES',
    err_log_table_name  => 'ERR$_SALES',
    err_log_table_space => 'DATA_TS',
    skip_unsupported    => TRUE
  );
END;
/

DESC ERR$_SALES

-- 代表的な制御列
-- ORA_ERR_NUMBER$  : エラー番号
-- ORA_ERR_MESG$    : エラーメッセージ
-- ORA_ERR_ROWID$   : エラー行のROWID。INSERT失敗ではNULLになりやすい
-- ORA_ERR_OPTYP$   : 操作種別。I/U/Dなど
-- ORA_ERR_TAG$     : LOG ERRORS句で指定した任意タグ

SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'ERR$_SALES'
ORDER BY column_id;

INSERT INTO sales(id, cust_id, amount, sale_dt)
SELECT id, cust_id, amount, sale_dt
FROM sales_stg
LOG ERRORS INTO err$_sales ('LOAD_20260527')
REJECT LIMIT UNLIMITED;

COMMIT;

MERGE INTO customers d
USING (
  SELECT cust_id, name, email, updated_at
  FROM customers_delta
) s
ON (d.cust_id = s.cust_id)
WHEN MATCHED THEN
  UPDATE SET d.name = s.name,
             d.email = s.email,
             d.updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (cust_id, name, email, updated_at)
  VALUES (s.cust_id, s.name, s.email, s.updated_at)
LOG ERRORS INTO err$_customers ('CUSTOMER_DELTA_20260527')
REJECT LIMIT UNLIMITED;

FORALL i IN 1 .. l_rows.COUNT
  INSERT INTO sales(id, cust_id, amount, sale_dt)
  VALUES (
    l_rows(i).id,
    l_rows(i).cust_id,
    l_rows(i).amount,
    l_rows(i).sale_dt
  )
  LOG ERRORS INTO err$_sales ('FORALL_20260527')
  REJECT LIMIT UNLIMITED;

SELECT ora_err_tag$,
       ora_err_number$,
       SUBSTR(ora_err_mesg$, 1, 200) AS err_msg,
       COUNT(*) AS err_count
FROM err$_sales
GROUP BY ora_err_tag$,
         ora_err_number$,
         SUBSTR(ora_err_mesg$, 1, 200)
ORDER BY err_count DESC;

SELECT ora_err_number$,
       ora_err_mesg$,
       ora_err_rowid$,
       ora_err_optyp$,
       ora_err_tag$,
       id,
       cust_id,
       amount,
       sale_dt
FROM err$_sales
WHERE ora_err_tag$ = 'LOAD_20260527'
ORDER BY ora_err_number$, id;

-- 1. エラーを隔離キューへ保存
INSERT INTO sales_error_queue(
  load_tag,
  err_number,
  err_message,
  sales_id,
  cust_id,
  amount_text,
  reason,
  created_at
)
SELECT ora_err_tag$,
       ora_err_number$,
       SUBSTR(ora_err_mesg$, 1, 1000),
       id,
       cust_id,
       amount,
       'needs review',
       SYSTIMESTAMP
FROM err$_sales
WHERE ora_err_tag$ = 'LOAD_20260527';

-- 2. 修正済みデータを再投入用ステージングへ
INSERT INTO sales_stg_retry(id, cust_id, amount, sale_dt)
SELECT sales_id,
       cust_id,
       TO_NUMBER(amount_text),
       SYSDATE
FROM sales_error_queue
WHERE load_tag = 'LOAD_20260527'
  AND reason = 'fixed';

-- 3. 対象タグのERR$_表を保持期限に応じて削除
DELETE FROM err$_sales
WHERE ora_err_tag$ = 'LOAD_20260527';

-- 例: ジョブ名_対象日_ステップ_実行ID
LOG ERRORS INTO err$_sales (
  'SALES_LOAD_20260527_STEP1_RUN00042'
)
REJECT LIMIT UNLIMITED

ALTER TABLE err$_sales ADD (
  logged_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE INDEX err_sales_ix1
ON err$_sales(ora_err_tag$, ora_err_number$);

CREATE INDEX err_sales_ix2
ON err$_sales(logged_at);

-- 90日を超えたログを削除
DELETE FROM err$_sales
WHERE logged_at < SYSTIMESTAMP - INTERVAL '90' DAY;

INSERT INTO load_job_summary(
  load_tag,
  target_table,
  success_count,
  error_count,
  status,
  created_at
)
SELECT 'LOAD_20260527',
       'SALES',
       SQL%ROWCOUNT,
       (SELECT COUNT(*) FROM err$_sales WHERE ora_err_tag$ = 'LOAD_20260527'),
       CASE
         WHEN (SELECT COUNT(*) FROM err$_sales WHERE ora_err_tag$ = 'LOAD_20260527') = 0
         THEN 'SUCCESS'
         ELSE 'WARNING'
       END,
       SYSTIMESTAMP
FROM dual;