Oracle DBMS_JOB 利用 BROKEN 參數實現一次性任務保留紀錄

問題背景

一般一次性 Job 執行後即從 USER_JOBS 消失，若需保留執行紀錄供審計，需自建 Log 表。利用 Job 內部 BROKEN 參數可讓 Job 執行一次後標記為 Broken 而保留於視圖中。

核心機制

Job 內部 PL/SQL 區塊可存取隱藏參數 BROKEN (BOOLEAN OUT)，設為 TRUE 即標記 Job 為 Broken，不再重跑，但紀錄保留。

實作範例

-- 1. 建立測試程序：模擬工作 5 秒，結束時設定 BROKEN=TRUE
CREATE OR REPLACE PROCEDURE P_TEST(P_BROKEN OUT BOOLEAN) AS
BEGIN
  P_BROKEN := TRUE;           -- 關鍵：標記為 Broken
  DBMS_LOCK.SLEEP(5);         -- 模擬工作
END;
/

-- 2. 提交 Job，Interval 設為 1 分鐘（實際只跑一次）
DECLARE
  V_JOB NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(V_JOB, 'P_TEST(BROKEN);', SYSDATE, 'SYSDATE + 1/1440');
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Job ' || V_JOB || ' submitted.');
END;
/

-- 3. 觀察狀態變化
SELECT JOB, WHAT, TOTAL_TIME, BROKEN, FAILURES FROM USER_JOBS;
-- 執行前：BROKEN = 'N'
-- 執行後：BROKEN = 'Y', FAILURES = 0, 紀錄保留

進階：帶參數的一次性 Job

CREATE OR REPLACE PROCEDURE PROCESS_BATCH(
  P_BATCH_ID IN NUMBER,
  P_BROKEN   OUT BOOLEAN
) AS
BEGIN
  -- 業務邏輯
  INSERT INTO BATCH_LOG VALUES (P_BATCH_ID, SYSDATE, 'STARTED');
  -- ... 處理 ...
  INSERT INTO BATCH_LOG VALUES (P_BATCH_ID, SYSDATE, 'COMPLETED');
  P_BROKEN := TRUE;  -- 完成後標記 Broken
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO BATCH_LOG VALUES (P_BATCH_ID, SYSDATE, 'FAILED: ' || SQLERRM);
    P_BROKEN := TRUE;  -- 失敗也標記，避免無限重試
    RAISE;             -- 保留 FAILURES 計數
END;
/

-- 提交
DECLARE V_JOB NUMBER; BEGIN
  DBMS_JOB.SUBMIT(V_JOB, 'PROCESS_BATCH(123, BROKEN);', SYSDATE, NULL); -- NULL = 不重複
  COMMIT;
END;
/

優勢總結

• ✅ 無需額外 Log 表即可查詢歷史執行紀錄。
• ✅ FAILURES 欄位自動記錄失敗次數。
• ✅ 可透過 DBMS_JOB.BROKEN(job, FALSE) 手動重啟。
• ⚠️ 僅適用 DBMS_JOB，DBMS_SCHEDULER (10g+) 應使用 AUTO_DROP = FALSE 屬性。

DBMS_SCHEDULER 寫法（10g+ 推薦）

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'ONCE_JOB_123',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'PROCESS_BATCH',
    number_of_arguments => 1,
    start_date      => SYSDATE,
    repeat_interval => NULL,          -- 不重複
    auto_drop       => FALSE,         -- 完成後保留
    enabled         => TRUE
  );
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('ONCE_JOB_123', 1, 123);
END;
/