Oracle Java Stored Procedure 完整實戰：SQL 創建、外部 Class 載入、權限授與與除錯

三種建立 Java Stored Procedure 方式

方式一：直接內嵌 Java 原始碼 (CREATE JAVA SOURCE)

-- 1. 編譯並載入 Java Source
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "JavaDemo1" AS
import java.sql.*;
public class JavaDemo1 {
    public static void main(String[] argv) {
        System.out.println("hello, Java demo1");
    }
};
/

-- 2. 檢查編譯錯誤
SHOW ERRORS JAVA SOURCE "JavaDemo1";

-- 3. 建立 PL/SQL Wrapper Procedure
CREATE OR REPLACE PROCEDURE JavaDemo1
AS LANGUAGE JAVA NAME 'JavaDemo1.main(java.lang.String[])';
/

-- 4. 執行與輸出導向
SET SERVEROUTPUT ON SIZE 5000;
CALL DBMS_JAVA.SET_OUTPUT(5000);
CALL JavaDemo1();
-- 輸出: hello, Java demo1

關鍵要求：方法必須 public static，參數型別對應 Java 陣列 String[]。

方式二：外部 .class 檔案載入 (CREATE JAVA CLASS USING BFILE)

-- 1. 準備 .class 檔放置於資料庫伺服器目錄
--    public class OracleJavaProc { public static void main(String[] argv) { ... } }

-- 2. 建立 Directory Object 指向該目錄 (需 CREATE ANY DIRECTORY 權限)
GRANT CREATE ANY DIRECTORY TO scott;
CONNECT scott/tiger@orcl;
CREATE OR REPLACE DIRECTORY test_dir AS 'd:\oracle';  -- Windows 路徑

-- 3. 從 BFILE 載入 Class
CREATE OR REPLACE JAVA CLASS USING BFILE (test_dir, 'OracleJavaProc.class');
/

-- 4. 建立 Wrapper Procedure
CREATE OR REPLACE PROCEDURE testJavaproc
AS LANGUAGE JAVA NAME 'OracleJavaProc.main(java.lang.String[])';
/

-- 5. 執行
SET SERVEROUTPUT ON SIZE 5000;
CALL DBMS_JAVA.SET_OUTPUT(5000);
CALL testJavaproc();
-- 或 EXEC testJavaproc;

方式三：loadjava 命令列工具 (批量部署首選)

# 載入單一 .class
loadjava -user scott/tiger@orcl -resolve OracleJavaProc.class

# 載入整個 JAR
loadjava -user scott/tiger@orcl -resolve myapp.jar

# 移除
dropjava -user scott/tiger@orcl OracleJavaProc.class

優點：支援 JAR、自動解析相依、命令列自動化部署、CI/CD 整合友善。

權限模型與安全

實務應用場景

1. 複雜演算法/加密/壓縮：Java 豐富函式庫 (Apache Commons, BouncyCastle) 處理 PL/SQL 難以實作的邏輯。
2. 外部系統整合：Java 呼叫 REST/gRPC/SOAP，再將結果寫回資料庫。
3. 檔案處理：解析 Excel/PDF/XML、產生報表檔案。
4. 正則表達式進階：java.util.regex 功能遠強於 REGEXP_LIKE。

除錯與監控

• SHOW ERRORS JAVA SOURCE/CLASS "name"：編譯錯誤
• SELECT * FROM USER_JAVA_CLASSES：已載入類別清單
• SELECT * FROM USER_ERRORS WHERE TYPE='JAVA SOURCE'：錯誤明細
• System.out/err 導向 DBMS_JAVA.SET_OUTPUT → DBMS_OUTPUT 顯示
• 遠端除錯 (JPDA)：JVM 啟動參數 -agentlib:jdwp=transport=dt_socket,server=y,suspend=n,address=8000 (需重啟資料庫，僅限開發環境)

效能考量

• Java Stored Procedure 在資料庫 JVM 執行，**避免網路往返**，適合資料密集型運算。
• 大量資料處理時，建議在 Java 端使用 JDBC 批次 (PreparedStatement.addBatch) 寫入。
• 避免在 Java 中開啟新資料庫連線，使用 defaultConnection() 共用 Session。
• JVM Heap 由 JAVA_POOL_SIZE 控制，大量載入 Class/JAR 需調大。