Oracle 8i 利用 DBMS_PIPE 實現存儲過程呼叫作業系統命令（上）

背景與動機

Oracle 8i 無內建指令呼叫 OS 命令功能。雖可用 OEM 排程作業，但參數固定缺乏彈性。利用 DBMS_PIPE 建立資料庫會話與外部守護進程的雙向通訊管道，可在 PL/SQL 中動態執行任意 OS 命令。

核心架構

• 資料庫端：PL/SQL 封裝 DAEMON，提供 EXECUTE_SYSTEM(cmd, timeout) 函數與 STOP 程序。
• OS 端：Pro*C 撰寫的守護進程，持續監聽 daemon 管道，接收命令、執行、回傳結果碼。

資料庫端 PL/SQL 封裝 (DAEMON)

CREATE OR REPLACE PACKAGE BODY daemon AS
  FUNCTION execute_system(command VARCHAR2, timeout NUMBER DEFAULT 10) RETURN NUMBER IS
    status NUMBER; result VARCHAR2(20); command_code NUMBER; pipe_name VARCHAR2(30);
  BEGIN
    pipe_name := DBMS_PIPE.UNIQUE_SESSION_NAME;
    DBMS_PIPE.PACK_MESSAGE('SYSTEM');
    DBMS_PIPE.PACK_MESSAGE(pipe_name);
    DBMS_PIPE.PACK_MESSAGE(command);
    status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
    IF status <> 0 THEN RAISE_APPLICATION_ERROR(-20010, 'Send error: '||status); END IF;

    status := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, timeout);
    IF status <> 0 THEN RAISE_APPLICATION_ERROR(-20011, 'Receive error: '||status); END IF;

    DBMS_PIPE.UNPACK_MESSAGE(result);
    IF result <> 'done' THEN RAISE_APPLICATION_ERROR(-20012, 'Done not received'); END IF;

    DBMS_PIPE.UNPACK_MESSAGE(command_code);
    DBMS_OUTPUT.PUT_LINE('System command executed. result = '||command_code);
    RETURN command_code;
  END;

  PROCEDURE stop(timeout NUMBER DEFAULT 10) IS
    status NUMBER;
  BEGIN
    DBMS_PIPE.PACK_MESSAGE('STOP');
    status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
    IF status <> 0 THEN RAISE_APPLICATION_ERROR(-20030, 'Stop send error: '||status); END IF;
  END;
END daemon;

關鍵點：使用 UNIQUE_SESSION_NAME 產生專屬回應管道，避免多會話干擾；封裝「命令類型、回應管道、命令內容」三訊息發送；同步等待回應並解包結果碼。

OS 端守護進程 (Pro*C 骨架)

#include <stdio.h> #include <stdlib.h> EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
  char *uid = "scott/tiger@service";  -- 需修改為實際連線串
  int status; VARCHAR command; VARCHAR value[2000]; VARCHAR return_name[30];
EXEC SQL END DECLARE SECTION;

void connect_error() { ... }  -- 錯誤處理範例

完整 Pro*C 碼需：
1. EXEC SQL CONNECT :uid 連線資料庫。
2. 迴圈 DBMS_PIPE.RECEIVE_MESSAGE('daemon') 等待請求。
3. UNPACK_MESSAGE 取得類型、回應管道、命令。
4. 若類型 SYSTEM → system(cmd) 執行，回傳碼 PACK_MESSAGE('done') + PACK_MESSAGE(rc) → SEND_MESSAGE(return_name)。
5. 若類型 STOP → 跳出迴圈結束。
6. 編譯：proc iname=daemon.pc → 編譯產生的 .c → 連結 Oracle 客戶端庫產生執行檔。

使用範例

-- SQL*Plus 中呼叫
VARIABLE rc NUMBER;
EXEC :rc := daemon.execute_system('ls -l /tmp > /tmp/ls.out', 30);
PRINT rc;

-- 停止守護進程
EXEC daemon.stop;

安全與限制提醒

• 守護進程以資料庫 OS 用戶身份執行，擁有該用戶所有權限，需嚴格控管部署環境。
• 資料庫端呼叫者需具備 EXECUTE ON DAEMON 權限。
• 管道通訊為同步阻塞，長時間命令會佔用資料庫會話；可考慮非同步設計或縮短 timeout。
• 9i 後可改用 DBMS_SCHEDULER 外部作業、Java Stored Procedure 等更標準方式。

下篇預告

下篇將提供完整可編譯的 Pro*C 守護進程代碼、編譯連結腳本、多會話併發測試與效能調優建議。