Oracle 外部過程實戰：配置 Listener/TNS、調用 COM 對象操作 Excel 完整指南

前言：打破 PL/SQL 能力邊界

Oracle 8+ 支持外部 C 過程，8i+ 引入 Java，Windows 平台更提供COM 自動化接口，允許 PL/SQL 直接操作 Excel/Word/Outlook 等 Office 組件。本文完整演示：Listener/TNS 配置、COM 包裝器安裝、PL/SQL 調用 Excel 導出實戰。

一、 運行環境與前置條件

• Oracle 9i / Windows 2000
• ORACLE_HOME=D:\Oracle\ora92, SID=ORADB, Host=CHANET
• 需監聽器啟用 IPC 協議 以啟動 extproc 進程

二、 核心配置：Listener.ora 與 TNSNames.ora

1. Listener.ora (關鍵：SID_LIST 增加 extproc)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST = (ADDRESS = (PROTOCOL=TCP)(HOST=CHANET)(PORT=1521)))
      (ADDRESS_LIST = (ADDRESS = (PROTOCOL=IPC)(KEY=extproc1)))  -- 必須：IPC 啟動 extproc
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =                     -- 外部過程專用 SID
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Oracle\ora92)
      (PROGRAM = extproc)           -- 必須：啟動 extproc 進程
    )
    (SID_DESC =                     -- 數據庫實例
      (GLOBAL_DBNAME = ORADB)
      (ORACLE_HOME = D:\Oracle\ora92)
      (SID_NAME = ORADB)
    )
  )

-- 可選優化參數
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
PASSWORDS_LISTENER = (ORACLE)

2. TNSNames.ora (外部過程連接描述符)

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL=IPC)(KEY=EXTPROC1)))  -- 與 Listener KEY 一致
    (CONNECT_DATA =
      (SID = PLSExtProc)          -- 與 Listener SID_NAME 一致
      (PRESENTATION = RO)         -- 必須：僅遠程操作
    )
  )

3. 驗證配置

LSNRCTL STOP
LSNRCTL START
TNSPING EXTPROC_CONNECTION_DATA
-- OK (30ms) 表示配置生效

-- 測試 extproc 進程
D:\Oracle\ora92\bin> extproc
-- 顯示版本信息即正常

三、 COM 自動化包裝器安裝 (官方提供)

SQL> CONNECT SYSTEM/chanet@ORADB
SQL> CREATE USER chanet IDENTIFIED BY chanet;
SQL> GRANT CREATE LIBRARY TO chanet;
SQL> CONNECT chanet/chanet@ORADB
SQL> @D:\Oracle\ora92\com\comwrap.sql
-- 此腳本創建 ORDCOM 包，封裝 COM 對象創建/屬性/方法調用

四、 PL/SQL 與 COM 數據類型映射表

五、 COM 核心 API 函數 (ORDCOM 包)

六、 實戰：PL/SQL 導出數據到 Excel 文件

對象模型鏈路

Application → Workbooks → Worksheet → Range → Font → Size

完整代碼

CREATE OR REPLACE PROCEDURE export_emp_to_excel(p_file VARCHAR2) AS
  v_app       BINARY_INTEGER;  -- COM 對象句柄用整數存儲
  v_wb        BINARY_INTEGER;
  v_ws        BINARY_INTEGER;
  v_range     BINARY_INTEGER;
  v_font      BINARY_INTEGER;
  v_sql       VARCHAR2(200);
  v_cur       INTEGER;
  v_col_cnt   INTEGER;
  v_desc      DBMS_SQL.DESC_TAB;
  v_val       VARCHAR2(4000);
  v_row       NUMBER := 1;
BEGIN
  -- 1. 創建 Excel Application
  v_app := ORDCOM.CreateObject('Excel.Application');
  ORDCOM.SetProperty(v_app, 'Visible', 0);  -- 後台運行
  
  -- 2. 添加 Workbook
  v_wb := ORDCOM.GetProperty(v_app, 'Workbooks');
  v_wb := ORDCOM.Invoke(v_wb, 'Add');
  
  -- 3. 獲取 ActiveSheet
  v_ws := ORDCOM.GetProperty(v_app, 'ActiveSheet');
  
  -- 4. 寫入表頭 (動態獲取列名)
  v_sql := 'SELECT * FROM emp';
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur, v_sql, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(v_cur, v_col_cnt, v_desc);
  
  FOR i IN 1..v_col_cnt LOOP
    v_range := ORDCOM.GetProperty(v_ws, 'Cells');
    v_range := ORDCOM.Invoke(v_range, 'Item', v_row, i);  -- Range(1, i)
    ORDCOM.SetProperty(v_range, 'Value', v_desc(i).col_name);
    -- 設置表頭加粗
    v_font := ORDCOM.GetProperty(v_range, 'Font');
    ORDCOM.SetProperty(v_font, 'Bold', 1);
  END LOOP;
  v_row := 2;
  
  -- 5. 循環寫入數據
  WHILE DBMS_SQL.FETCH_ROWS(v_cur) > 0 LOOP
    FOR i IN 1..v_col_cnt LOOP
      DBMS_SQL.COLUMN_VALUE(v_cur, i, v_val);
      v_range := ORDCOM.GetProperty(v_ws, 'Cells');
      v_range := ORDCOM.Invoke(v_range, 'Item', v_row, i);
      ORDCOM.SetProperty(v_range, 'Value', v_val);
    END LOOP;
    v_row := v_row + 1;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(v_cur);
  
  -- 6. 保存文件
  v_wb := ORDCOM.GetProperty(v_app, 'ActiveWorkbook');
  ORDCOM.Invoke(v_wb, 'SaveAs', p_file);  -- 完整路徑
  
  -- 7. 關閉 Excel
  ORDCOM.Invoke(v_app, 'Quit');
  ORDCOM.DestroyObject(v_app);
  
EXCEPTION
  WHEN OTHERS THEN
    IF v_app != 0 THEN ORDCOM.DestroyObject(v_app); END IF;
    RAISE;
END;
/

七、 關鍵注意事項

八、 總結

外部過程 + COM 自動化實現了「PL/SQL 直接操作 Office」的獨特能力。**配置核心在於 Listener IPC + extproc SID + TNS EXTPROC_CONNECTION_DATA 三位一體**。實戰中，小報表/格式複雜/需公式/圖表 用 COM 導出 Excel；大數據量/高性能/純數據 用 CSV/外部表/ODP.NET。技術選型決定成敗。

1. 上一篇文章： Oracle 常見鎖模式速查手冊：7 級模式含義、觸發語句、診斷查殺腳本
2. 下一篇文章： Oracle 聯機備份 (Online Backup) 核心原理與實施全流程：ARCHIVELOG 模式下的數據零損失保障

Oracle 資料庫基礎

Oracle 數據庫性能模型構建：以響應時間為核心的容量規劃與風險預測

從 AWR 到性能模型：量化 DB Time、邏輯讀、IO

Oracle 匯出匯入字符集轉換規律全面剖析：避免資料損失與亂碼

四大關鍵字符集決定轉換行為 Exp/Imp 過程涉及四個字符

Oracle 字串搜尋效能實測：INSTR 完勝 LIKE，1100 萬筆實測差距 3 倍

實測場景 測試表 T，資料量 1,100 萬筆 欄

Oracle SPFILE 損壞恢復：從 PFILE 重建 SPFILE 完整實操

誤設 SGA_MAX_SIZE、SHARED_POOL_SI