Oracle Java 存儲過程訪問異構數據庫 (SQL Server) 完整實戰指南

前言：打破數據孤島

企業常面臨 Oracle 與 SQL Server 共存需跨庫查詢的場景。除 Linked Server、Transparent Gateway 外，Oracle Java 存儲過程 + JDBC 提供了一種跨平台、無需額外授權、邏輯封裝在數據庫層的靈活方案。本文以 Oracle 8.1.7 (Windows 2000) 連接 SQL Server 為例，完整演示安裝、授權、開發、部署、調用全流程。

一、 環境準備與 JServer 安裝

1. 安裝 JServer：典型安裝默認包含；自定義安裝勾選 JServer 組件。或手動執行 @$ORACLE_HOME/javavm/install/initjvm.sql (約 10 分鐘，先閱讀 readme.txt)。
2. 驗證：DBA Studio 中出現 JServer 節點即成功。

二、 權限授予

-- JServer 安裝後自動創建角色
GRANT JAVASYSPRIV TO app_user;  -- 系統級 Java 權限 (慎用)
GRANT JAVAUSERPRIV TO app_user; -- 用戶級 Java 權限 (標準)
-- 若需網絡/文件訪問，還需授予 JAVADEPLOY、JAVADPRIV 等細粒度權限 (DBMS_JAVA.GRANT_PERMISSION)

三、 Java 類開發：封裝 SQL Server 訪問邏輯

import java.sql.*;

public class SqlServerBridge {
    // 單行查詢示例：返回拼接字符串 (9i 以下無結果集映射類型)
    public static String getStringFromSqlServer() {
        String result = "";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 1. 加載驅動 (需提前 loadjava 驅動 JAR: mssqlserver.jar / jtds.jar)
            Class.forName("com.inet.tds.TdsDriver").newInstance(); // iNet UNA 驅動示例
            
            // 2. 建立連接
            String url = "jdbc:inetdae7:10.24.09.192:1433?database=pubs";
            String user = "sa";
            String pwd = "123";
            conn = DriverManager.getConnection(url, user, pwd);
            
            // 3. 執行查詢
            String sql = "SELECT TOP 1 dtype, dname FROM test";
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery(sql);
            
            if (rs.next()) {
                result = "The result from sql server is: dtype->" + rs.getString("dtype") 
                       + " dname->" + rs.getString("dname");
            }
        } catch (Exception e) {
            e.printStackTrace();
            result = "Operator fail in " + e.getClass().getSimpleName();
        } finally {
            // 4. 嚴格關閉資源
            try { if (rs != null) rs.close(); } catch (Exception ignore) {}
            try { if (stmt != null) stmt.close(); } catch (Exception ignore) {}
            try { if (conn != null) conn.close(); } catch (Exception ignore) {}
        }
        return result;
    }
}

關鍵點提示

• 驅動 JAR 必須先加載數據庫：loadjava -u user/pwd -resolve mssqlserver.jar，否則 ClassNotFoundException。
• 結果集映射限制：9i 以前無 SYS_REFCURSOR 對應 Java ResultSet 映射，變通方案：返回拼接字符串 或 寫入 Oracle 暫存表 再由 PL/SQL 讀取。
• 異常處理：捕獲具體異常類型，返回可讀錯誤信息，避免調用端只見 "Java Stored Procedure Error"。

四、 部署與發布

-- 1. 加載業務類
loadjava -u app_user/pwd@orcl -v -resolve SqlServerBridge.java

-- 2. 發布 Call Spec
CREATE OR REPLACE FUNCTION get_sqlserver_data RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'SqlServerBridge.getStringFromSqlServer() return java.lang.String';
/

五、 測試調用

SET SERVEROUTPUT ON SIZE 5000
CALL dbms_java.set_output(5000);

-- 方式一
EXECUTE get_sqlserver_data;

-- 方式二
CALL get_sqlserver_data();

-- 方式三 (SQL 調用)
SELECT get_sqlserver_data() FROM dual;

六、 外部應用調用 (JDBC/ODP.NET)

調用方式與普通 PL/SQL 存儲過程完全一致：

// Java JDBC
CallableStatement cs = conn.prepareCall("{? = call get_sqlserver_data()}");
cs.registerOutParameter(1, Types.VARCHAR);
cs.execute();
String result = cs.getString(1);

// C# ODP.NET
OracleCommand cmd = new OracleCommand("get_sqlserver_data", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ret", OracleDbType.Varchar2, ParameterDirection.ReturnValue);
cmd.ExecuteNonQuery();
string result = cmd.Parameters["ret"].Value.ToString();

七、 進階議題與替代方案對比

八、 總結

Oracle Java 存儲過程通過 jdbc:default:connection: 內部連接與外部 JDBC 連接雙通道，優雅實現異構數據庫訪問。核心成功要素：驅動 JAR 先行加載、資源嚴格關閉、結果集變通映射、權限最小化授予。對於「非 Windows 平台 Oracle 查 SQL Server」這一經典痛點，它是成本最低、靈活性最高的方案。