Oracle DBMS_JOB 與 DBMS_PIPE 實現長時間任務非同步監控

架構概覽

解決客戶端呼叫長時間儲存程序無回應、無進度回報的問題：

1. DBMS_JOB 將主處理程序提交至 Job Queue 背景執行。
2. 主程序透過 DBMS_PIPE 定期寫入進度訊息。
3. 監控程序讀取 Pipe 即時顯示進度。
4. 客戶端輪詢監控程序取得狀態。

環境準備

-- 1. 啟用 Job Queue（需重啟實例）
-- 9i/10g: JOB_QUEUE_PROCESSES = 10
-- 8i: 另需 JOB_QUEUE_INTERVAL = 1

-- 2. 授權必要權限
GRANT EXECUTE ON DBMS_PIPE  TO app_user;
GRANT EXECUTE ON DBMS_LOCK  TO app_user;
GRANT EXECUTE ON DBMS_JOB   TO app_user;

核心程序實作

1. 模擬長時間任務

CREATE OR REPLACE PROCEDURE Long_Running_Task IS
  v_status NUMBER;
BEGIN
  FOR i IN 1..30 LOOP
    DBMS_PIPE.PURGE('task_pipe');
    DBMS_PIPE.PACK_MESSAGE(i);
    v_status := DBMS_PIPE.SEND_MESSAGE('task_pipe');
    DBMS_LOCK.SLEEP(1); -- 模擬工作
  END LOOP;
  -- 完成標記
  DBMS_PIPE.PURGE('task_pipe');
  DBMS_PIPE.PACK_MESSAGE(999999);
  v_status := DBMS_PIPE.SEND_MESSAGE('task_pipe');
END;
/

2. 監控程序（非阻塞讀取）

CREATE OR REPLACE PROCEDURE Get_Task_Progress(p_progress OUT NUMBER) IS
  v_status NUMBER;
  v_msg    NUMBER;
BEGIN
  -- 等待 0 秒：有訊息即讀，無則立即返回
  v_status := DBMS_PIPE.RECEIVE_MESSAGE('task_pipe', 0);
  IF v_status != 0 THEN
    p_progress := -1; -- 無訊息或逾時
    RETURN;
  END IF;
  DBMS_PIPE.UNPACK_MESSAGE(v_msg);
  p_progress := v_msg;
END;
/

3. 啟動器：提交 Job

CREATE OR REPLACE PROCEDURE Start_Task(p_job_no OUT NUMBER) IS
BEGIN
  DBMS_JOB.SUBMIT(p_job_no, 'Long_Running_Task;', SYSDATE, NULL, FALSE);
  COMMIT; -- 必須 Commit Job 才會被 Job Queue Process 拾取
END;
/

客戶端呼叫流程

-- 1. 啟動任務
VAR job_no NUMBER;
EXEC Start_Task(:job_no);
PRINT job_no;

-- 2. 輪詢進度
VAR progress NUMBER;
BEGIN
  LOOP
    EXEC Get_Task_Progress(:progress);
    EXIT WHEN :progress IN (-1, 999999);
    DBMS_OUTPUT.PUT_LINE('Progress: ' || :progress);
    DBMS_LOCK.SLEEP(2);
  END LOOP;
  IF :progress = 999999 THEN
    DBMS_OUTPUT.PUT_LINE('Task Completed!');
  END IF;
END;
/

關鍵點總結

• RECEIVE_MESSAGE(pipe, 0) 參數 0 代表非阻塞，關鍵避免監控程序卡住。
• Job 提交後必須 COMMIT，否則不會被執行。
• Pipe 名稱建議加上 Session ID 或任務 ID 避免衝突：'task_pipe_' || DBMS_SESSION.UNIQUE_SESSION_ID。
• 生產環境建議改用 DBMS_SCHEDULER（10g+）功能更強大。