Oracle 非同步預存程序實戰：DBMS_JOB + DBMS_PIPE 實現長任務監控

業務痛點

客戶端呼叫大型預存程序（跑批、報表、ETL），**同步阻塞**導致：

• 前端逾時（ADO/ODBC/JDBC 默認逾時 30~60 秒）。
• 無進度回饋，使用者以為死當。
• 無法中斷或重試。

架構設計：Job + Pipe 非同步模式

Client → BEGINMAXPRO() → DBMS_JOB.SUBMIT → Job Queue → MAXPRO() (Background)
                                      ↓
                              DBMS_PIPE.PACK/SEND → Pipe 'maxpro'
                                      ↓
Client → READMAXPRO() → DBMS_PIPE.RECEIVE/UNPACK → 進度顯示

環境準備

1. 啟用 Job Queue：
   • 9i+：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. 主處理程序 MAXPRO（模擬 30 秒長任務）

CREATE OR REPLACE PROCEDURE maxpro AS
  n      INTEGER;
  status NUMBER;
BEGIN
  DBMS_PIPE.PURGE('maxpro');  -- 清舊訊息
  FOR n IN 1..30 LOOP
    DBMS_PIPE.PACK_MESSAGE(n);
    status := DBMS_PIPE.SEND_MESSAGE('maxpro');
    DBMS_LOCK.SLEEP(1);       -- 模擬工作 1 秒
  END LOOP;
  DBMS_PIPE.PURGE('maxpro');
  DBMS_PIPE.PACK_MESSAGE(999999);  -- 完成標記
  status := DBMS_PIPE.SEND_MESSAGE('maxpro');
END maxpro;
/

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

CREATE OR REPLACE PROCEDURE readmaxpro(p_progress OUT INTEGER) AS
  n      INTEGER;
  status INTEGER;
BEGIN
  -- timeout=0 非阻塞：無訊息立即返回 status=1
  status := DBMS_PIPE.RECEIVE_MESSAGE('maxpro', 0);
  IF status != 0 THEN
    p_progress := -1;  -- 管道無資料
    RETURN;
  END IF;
  DBMS_PIPE.UNPACK_MESSAGE(p_progress);
END readmaxpro;
/

關鍵：**RECEIVE_MESSAGE(pipe, 0) 秒數為 0**，表示不等待，立即返回狀態碼：

• 0 = 成功讀到訊息
• 1 = 逾時無訊息
• 2 = 訊息過大
• 3 = 內部錯誤

3. 啟動器 BEGINMAXPRO（提交 Job 立即返回）

CREATE OR REPLACE PROCEDURE beginmaxpro AS
  v_job BINARY_INTEGER;
BEGIN
  DBMS_JOB.SUBMIT(v_job, 'maxpro;', SYSDATE, NULL, FALSE);
  DBMS_OUTPUT.PUT_LINE('Job Queue No: ' || v_job);
  COMMIT;  -- Job 提交必須 COMMIT 才生效
END beginmaxpro;
/

實測流程與輸出

SQL> SET SERVEROUTPUT ON
SQL> CALL beginmaxpro();
Job Queue No: 21
Call completed.

SQL> CALL readmaxpro();
maxpro 目前進度: 14
Call completed.

SQL> CALL readmaxpro();
maxpro 目前進度: 16
Call completed.

SQL> CALL readmaxpro();
maxpro 目前進度: 23
Call completed.

SQL> CALL readmaxpro();
maxpro 目前進度: 999999  -- 完成
Call completed.

SQL> CALL readmaxpro();
管道中目前無訊息回傳
Call completed.

前端整合建議

• 改用 OUT 參數回傳進度（如上 p_progress OUT INTEGER），ADO/ODBC/JDBC 可直接取得回傳值。
• 前端輪詢間隔 1~2 秒，顯示進度條或百分比。
• 提供「取消」功能：DBMS_JOB.REMOVE(job_no) 或在 MAXPRO 內檢查控制表/管道指令實現優雅停止。
• 雙向通訊：監控端可 SEND_MESSAGE 指令至另一管道，主程序 RECEIVE_MESSAGE 檢查是否收到停止/暫停指令。

10g+ 現代化替代方案

• DBMS_SCHEDULER：取代 DBMS_JOB，支援鏈式任務、事件驅動、資源管理、日曆語法。
• DBMS_ALERT：替代 DBMS_PIPE，發布/訂閱模型，支援多消費者、信號通知。
• Application Continuity / TAF：連線故障自動重連，長任務更健壯。

結語

DBMS_JOB + DBMS_PIPE 是 Oracle 9i/10g 時代實現長任務非同步監控的經典組合。核心在於**「Job 脫離 Session 執行」+「Pipe 非阻塞狀態傳遞」**。理解 RECEIVE_MESSAGE(timeout=0) 非阻塞語意與 COMMIT 觸發 Job 執行的時機，即可構建穩定的背景作業監控體系。