Oracle Java Stored Procedure 實戰：調用外部程式實現 sqluldr2 導出→壓縮→郵件全自動化

業務場景

需求：Oracle 定時任務調用第三方工具 sqluldr2 高效導出大量數據 → WinRAR 壓縮加密 → 發送郵件。純 PL/SQL 無法直接執行 OS 命令，利用 **Java Stored Procedure (JSP)** 橋接 JVM 與作業系統。

架構設計

[DBMS_SCHEDULER] → [PL/SQL Wrapper] → [Java Source: jv_run_extpro] → [OS Process: sqluldr2 / WinRAR]
                                                    ↓
                                           [StreamGobbler 非同步讀取 stdout/stderr]
                                                    ↓
                                           [寫入 DBMS_OUTPUT / 日誌表]

核心 Java Source 代碼

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED jv_run_extpro AS
import java.io.*;
import java.lang.*;
import java.util.*;

public class jv_run_extpro {
    public static void run(String cmd) throws IOException {
        Process p = Runtime.getRuntime().exec(cmd);
        
        // 關鍵：非同步消耗 stdout/stderr，防止緩衝區填滿導致進程掛起
        StreamGobbler errorGobbler = new StreamGobbler(p.getErrorStream(), "ERROR");
        StreamGobbler outputGobbler = new StreamGobbler(p.getInputStream(), "OUTPUT");
        errorGobbler.start();
        outputGobbler.start();
        
        try { p.waitFor(); }
        catch (InterruptedException ie) { System.out.println(ie); }
    }

    static class StreamGobbler extends Thread {
        InputStream is; String type;
        StreamGobbler(InputStream is, String type) { this.is = is; this.type = type; }
        public void run() {
            try (BufferedReader br = new BufferedReader(new InputStreamReader(is))) {
                String line;
                while ((line = br.readLine()) != null) {
                    System.out.println(type.equals("ERROR") ? "ERROR: " + line : "DEBUG: " + line);
                }
            } catch (IOException ioe) { ioe.printStackTrace(); }
        }
    }
};

PL/SQL 封裝介面

CREATE OR REPLACE PROCEDURE pro_jv_run_extpro(p_cmd VARCHAR2) AS
LANGUAGE JAVA NAME 'jv_run_extpro.run(java.lang.String)';

授權與權限配置（關鍵步驟）

-- 1. 授予 Java 權限 (需 SYS 執行)
BEGIN
  DBMS_JAVA.GRANT_PERMISSION('APP_USER', 'SYS:java.io.FilePermission', '<>', 'read,write,execute');
  DBMS_JAVA.GRANT_PERMISSION('APP_USER', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
  DBMS_JAVA.GRANT_PERMISSION('APP_USER', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
  COMMIT;
END;
/

-- 2. 確保 JVM 已載入
SELECT * FROM DBA_REGISTRY WHERE COMP_ID='JAVAVM';  -- STATUS=VALID

實際調用範例

BEGIN
  -- 1. sqluldr2 導出 (高性能無需 SQL*Net 開銷)
  pro_jv_run_extpro('sqluldr264.exe scott/tiger@pdborcl query="select * from big_table" field=, head=yes file=D:\export\big_table.csv');
  
  -- 2. WinRAR 壓縮加密
  pro_jv_run_extpro('"C:\Program Files\WinRAR\Rar.exe" a -ep -hpMySecretPass "D:\export\big_table.rar" "D:\export\big_table.csv"');
  
  -- 3. 可擴展：調用 PowerShell 發送郵件
  -- pro_jv_run_extpro('powershell -Command "Send-MailMessage ..."');
END;
/

關鍵技術點深度解析

進階：回傳執行結果碼

-- 修改 Java 回傳 int
public static int run(String cmd) throws IOException {
    Process p = Runtime.getRuntime().exec(cmd);
    new StreamGobbler(p.getErrorStream(), "ERR").start();
    new StreamGobbler(p.getInputStream(), "OUT").start();
    return p.waitFor();  // 0=成功, 非0=失敗
}
-- PL/SQL 對應修改
LANGUAGE JAVA NAME 'jv_run_extpro.run(java.lang.String) return int';

安全性警示

• 極高風險：授予 `java.io.FilePermission <>` 相當於給資料庫用戶 OS 級讀寫執行權限
• **生產環境務必**：
  1. 專用低權限 OS 帳號運行 Oracle 服務
  2. 限制 Java 權限至特定目錄 DBMS_JAVA.GRANT_PERMISSION(..., 'D:\export\*', ...)
  3. 命令參數化，嚴禁動態拼接用戶輸入防注入
  4. 審計所有 `pro_jv_run_extpro` 呼叫記錄

總結

Java Stored Procedure 是 Oracle 突破「資料庫隔離 OS」的標準橋樑。**StreamGobbler 非同步讀取是穩定運行的核心**，權限最小化是安全底線。適用於：ETL 工具鏈整合、報表生成、檔案轉換、外部 API 調用等場景。若僅需簡單檔案操作，優先考慮 UTL_FILE、DBMS_SCHEDULER 外部作業、或 Oracle 19c+ DBMS_CLOUD 等原生方案。