Oracle PL/SQL 呼叫 COM 元件完整實作：從監聽器配置到 Excel 自動化範例

前言

Oracle 8i 引入外部程序機制，允許 PL/SQL 呼叫 C/Java 外部函數；在 Windows 平台上，更進一步支援透過 COM 自動化介面操作 Office 應用程式。本文完整記錄從監聽器配置、tnsnames 設定、外部程序驗證，到 COM 介面函數說明、資料型別對應、Excel 自動化實戰範例的端到端實作流程。

一、外部程序架構原理

• PL/SQL 呼叫外部函數 → 監聽器啟動 extproc 進程
• extproc 動態載入共享庫 (Windows: DLL)
• 進程做為緩衝：接收 PL/SQL 命令 → 傳遞給 DLL → 回傳結果
• 進程生命週期：隨會話建立，會話結束自動終止

二、必要配置：listener.ora

路徑：%ORACLE_HOME%\network\admin\listener.ora

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORADB)
      (ORACLE_HOME = D:\Oracle\ora92)
      (SID_NAME = ORADB)
    )
  )

關鍵參數解析

1. IPC 協議 + KEY=extproc1：專供外部程序通訊的本地通道
2. SID_NAME=PLSExtProc：外部程序專用 SID，固定值
3. PROGRAM=extproc：指定啟動 extproc 可執行檔

三、必要配置：tnsnames.ora

路徑：%ORACLE_HOME%\network\admin\tnsnames.ora

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

一致性檢查：KEY 與 SID 必須與 listener.ora 完全一致。

四、驗證外部程序環境

-- 1. 重啟監聽器
LSNRCTL> STOP
LSNRCTL> START

-- 2. 測試連線
C:\> tnsping EXTPROC_CONNECTION_DATA
-- 預期：OK (30 msec)

-- 3. 直接測試 extproc
D:\Oracle\ora92\bin> extproc
-- 預期：Heterogeneous Agent Release 9.2.0.1.0 - Production

五、COM 自動化介面函數說明

Oracle 提供 ORDCOM 套件封裝 COM 操作，核心函數如下：

函數	功能
CreateObject	建立 COM 物件實例 (ProgID 或 CLSID)
DestroyObject	釋放 COM 物件
GetLastError	取得最後錯誤訊息
GetProperty	取得屬性值
SetProperty	設定屬性值
InitArg	初始化 Invoke 參數
InitOutArg	初始化輸出參數
GetArg	取得輸出參數值
SetArg	設定 Invoke 輸入參數
Invoke	呼叫方法/屬性存取器

六、PL/SQL 與 COM 資料型別對應

PL/SQL	COM (Variant)
VARCHAR2	BSTR
BOOLEAN	BOOL
BINARY_INTEGER	BYTE, INT, LONG
DOUBLE PRECISION	DOUBLE, FLOAT, CURRENCY
DATE	DATE

七、環境建置腳本

-- 1. 建立用戶並授權
CONNECT SYSTEM/chanet@oradb
CREATE USER chanet IDENTIFIED BY chanet;
GRANT CREATE LIBRARY TO chanet;

-- 2. 執行 Oracle 內建 COM 包裝腳本
CONNECT chanet/chanet@oradb
@D:\Oracle\ora92\com\comwrap.sql

八、監聽器額外參數建議

STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
PASSWORDS_LISTENER = (Oracle)

九、Excel 自動化實戰範例：匯出查詢結果至 Excel

目標

將資料表記錄透過 PL/SQL 寫入 Excel 檔案，設定欄位標題、字型、欄寬。

COM 物件操作層級

1. Application (Excel.Application)
2. Workbook (Workbooks.Add)
3. Worksheet (ActiveSheet)
4. Range (Cells, Range("A1"))
5. Font (Range.Font)
6. 屬性/方法：Value, Font.Size, ColumnWidth, SaveAs, Quit

PL/SQL 實作邏輯

DECLARE
  v_app       BINARY_INTEGER;  -- Application 物件控制代碼
  v_wb        BINARY_INTEGER;  -- Workbook
  v_ws        BINARY_INTEGER;  -- Worksheet
  v_range     BINARY_INTEGER;  -- Range
  v_font      BINARY_INTEGER;  -- Font
  v_token     BINARY_INTEGER;  -- Invoke token
BEGIN
  -- 1. 建立 Excel Application
  ORDCOM.CreateObject('Excel.Application', 0, NULL, v_app);
  ORDCOM.SetProperty(v_app, 'Visible', 0, FALSE);  -- 背景執行

  -- 2. 新增 Workbook
  ORDCOM.InitArg;
  ORDCOM.Invoke(v_app, 'Workbooks.Add', v_token);
  ORDCOM.GetArg(v_token, 1, v_wb);

  -- 3. 取得 ActiveSheet
  ORDCOM.GetProperty(v_wb, 'ActiveSheet', v_ws);

  -- 4. 寫入標題列 (Range A1:C1)
  ORDCOM.GetProperty(v_ws, 'Range("A1:C1")', v_range);
  ORDCOM.SetProperty(v_range, 'Value', 0, 'ID,Name,Salary');
  ORDCOM.GetProperty(v_range, 'Font', v_font);
  ORDCOM.SetProperty(v_font, 'Bold', 0, TRUE);
  ORDCOM.SetProperty(v_font, 'Size', 0, 12);

  -- 5. 迴圈寫入資料列 (略，需游標查詢)

  -- 6. 儲存並關閉
  ORDCOM.InitArg;
  ORDCOM.SetArg(v_token, 1, 'D:\export.xls', 'BSTR');
  ORDCOM.Invoke(v_wb, 'SaveAs', v_token);
  ORDCOM.Invoke(v_app, 'Quit', v_token);

  -- 7. 釋放物件
  ORDCOM.DestroyObject(v_app);
EXCEPTION
  WHEN OTHERS THEN
    ORDCOM.GetLastError;
    RAISE;
END;

十、關鍵注意事項

1. 安全性：資料庫伺服器需安裝 Excel，且服務帳號需有桌面互動權限（或使用無介面的 Excel 替代方案）
2. 效能：COM 呼叫跨進程開銷大，大量資料建議改用 UTL_FILE 產生 CSV 或 XML Publisher
3. 穩定性：Excel 未正確關閉會殘留進程，需完善例外處理與 DestroyObject
4. 替代方案：Oracle 10g+ 推薦使用 APEX、ORDS、SQL Developer 匯出功能

結語

PL/SQL 呼叫 COM 開啟了資料庫與 Windows 生態整合的大門，特別是 Office 自動化報表場景極具實務價值。但架構複雜度高（監聽器、extproc、DLL、COM、Excel 進程），部署維護成本不容小覷。現代架構建議將報表產出移至應用層或專用報表服務，資料庫專注於資料處理。若必須在資料庫層實作，請嚴格遵循配置驗證流程、建立完善監控告警、並評估無介面替代方案（如 LibreOffice UNO、Python openpyxl 透過外部程序）。