Oracle PL/SQL 呼叫 COM 元件實作：以 Excel 自動化為例

背景與動機

PL/SQL 擅長資料庫邏輯，但需存取作業系統資源（檔案、Excel、Outlook 等）時，透過外部程序呼叫 C/C++ 或 COM 物件更為合適。Oracle 8i 引入 External Procedures 機制，簡化了跨語言呼叫。

外部程序運作原理

1. PL/SQL 呼叫外部函數
2. Listener 啟動 extproc 進程
3. extproc 動態載入 DLL (Windows) / Shared Library (Unix)
4. DLL 執行邏輯並回傳結果給 extproc
5. extproc 將結果返還給 PL/SQL

extproc 隨 Session 存續，Session 結束自動終止。

環境設定

1. 修改 listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\ora92)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = extproc1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = CHANET)(PORT = 1521))
  )

2. 修改 tnsnames.ora

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = extproc1))
    (CONNECT_DATA = (SID = PLSExtProc)(PRESENTATION = RO))
  )

3. 重啟 Listener 並測試

lsnrctl stop
lsnrctl start
tnsping EXTPROC_CONNECTION_DATA

COM 物件操作介面

Oracle 提供 ORDCOM 套件（C 外部程序）操作 COM 物件，三大核心動作：

• 取得屬性
• 設定屬性（唯讀屬性除外）
• 呼叫方法

PL/SQL 對應 COM 資料型別對照表

常用 ORDCOM 函數

• CreateObject(ProgID, ServerName)：建立 COM 物件實例
• GetProperty(ObjectToken, PropertyName, ...)：取得屬性
• SetProperty(ObjectToken, PropertyName, NewValue, ...)：設定屬性
• Invoke(ObjectToken, MethodName, ...)：呼叫方法
• DestroyObject(ObjectToken)：釋放物件

實作範例：自動化 Excel 建立報表

CREATE OR REPLACE PROCEDURE gen_excel_report(p_file VARCHAR2) AS
  v_app      BINARY_INTEGER;  -- COM 物件 Token
  v_wb       BINARY_INTEGER;
  v_ws       BINARY_INTEGER;
  v_range    BINARY_INTEGER;
BEGIN
  -- 1. 建立 Excel Application
  v_app := ORDCOM.CreateObject('Excel.Application', 'localhost');
  ORDCOM.SetProperty(v_app, 'Visible', 0);  -- 背景執行

  -- 2. 新增活頁簿與工作表
  v_wb := ORDCOM.Invoke(v_app, 'Workbooks.Add');
  v_ws := ORDCOM.GetProperty(v_wb, 'Worksheets', 1);
  ORDCOM.SetProperty(v_ws, 'Name', 'SalesReport');

  -- 3. 寫入標題列
  v_range := ORDCOM.GetProperty(v_ws, 'Range', 'A1:C1');
  ORDCOM.SetProperty(v_range, 'Value', ORDCOM.MakeSafeArray('Product,Qty,Amount'));

  -- 4. 從資料庫讀取資料並寫入（略，使用 FOR 迴圈搭配 SetProperty）

  -- 5. 儲存並關閉
  ORDCOM.Invoke(v_wb, 'SaveAs', p_file);
  ORDCOM.Invoke(v_wb, 'Close', FALSE);
  ORDCOM.Invoke(v_app, 'Quit');

  -- 6. 釋放資源
  ORDCOM.DestroyObject(v_range);
  ORDCOM.DestroyObject(v_ws);
  ORDCOM.DestroyObject(v_wb);
  ORDCOM.DestroyObject(v_app);
EXCEPTION
  WHEN OTHERS THEN
    -- 確保釋放 COM 物件
    IF v_range  IS NOT NULL THEN ORDCOM.DestroyObject(v_range);  END IF;
    IF v_ws     IS NOT NULL THEN ORDCOM.DestroyObject(v_ws);     END IF;
    IF v_wb     IS NOT NULL THEN ORDCOM.DestroyObject(v_wb);     END IF;
    IF v_app    IS NOT NULL THEN ORDCOM.DestroyObject(v_app);    END IF;
    RAISE;
END;
/

注意事項

• COM 物件必須在資料庫服務器端註冊（或遠端 DCOM 設定）
• extproc 進程以 Oracle 服務帳號執行，需具備存取 COM 元件權限
• 大量操作時注意效能，建議批次寫入而非逐儲存格
• 例外處理務必釋放所有 Token，避免記憶體洩漏導致 Excel 殘留進程