Oracle DB Call Java Class & ORA-29532

參考官方文件：Calling Java Methods in Oracle Database

1.寫了一個test.java，存在D:\Test底下

import java.io.*;
public class test {
   public static void main(String[] args) throws IOException {
   // TODO Auto-generated method stub
   //Hello World!!!!
      System.out.println("Hello World!!!!!!!!!!!");
      BufferedWriter out = new BufferedWriter(new FileWriter("D://output.txt"));
      try {
         String inputLine = "Hello World!!!!!!!!!!!";
         out.write(inputLine);
         out.newLine();
      } catch(IOException e1) {
         System.out.println("Error during reading/writing");
      } finally {
         out.close();
      }
   }
}

2.使用Oracle db內建的JDK把他編譯成test.class

PS.Oracle DB 11g的JVM版本，所以必須使用Oracle DB內建的SDK。
設定方式：

• 控制台→系統→進階系統設定

• 環境變數

• Path→編輯。在原本的值後面加上分號";"再加入Oracle DB java sdk位置。ex.D:\app\Kunde_Hong\product\11.2.0\dbhome_1\jre\1.5.0\bin

3.直接執行java，成功!並且在產生出D:\ output.txt。

4.登入sqlplus

5.建立相關的別名跟設定

l   create or replace directory java_dir as 'D:\Test';

l   create or replace java class using bfile(java_dir,'test.class');

l   create or replace procedure test_java as language java name 'test.main(java.lang.String[])';

6.執行，他有成功但是並沒有輸出Hello World!!!!看下是否產生出D:\ output.txt。

(要在SQL中執行java的輸出，必須再加上DBMS_JAVA.SET_OUTPUT(1000);)

PS.如果遇到ORA-29532 

請用SYS登入，然後執行

EXECUTE dbms_java.grant_permission( 'EEP', 'SYS:java.io.FilePermission', 'D:\output.txt', 'write' );

其中EEP為你的DB User's Name。

方法二

1.      直接寫成SQL script ，登入SqlPlus(或者使用Toad之類的工具)

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED EEP." TestJava" as
package Pkg1;
import java.io.*;
public class test {
 public static void main(String[] args) throws IOException {
    // TODO Auto-generated method stub
    //Hello World!!!!
    System.out.println("Hello World!!!!!!!!!!!");  
    BufferedWriter out = new BufferedWriter(new FileWriter("D://output.txt"));
    try {
        String inputLine = "Hello World!!!!!!!!!!!";
        out.write(inputLine);
        out.newLine();
    } catch(IOException e1) {
        System.out.println("Error during reading/writing");
    } finally {
        out.close();
    }
  }
}

2.      建立相關的別名跟設定，這次使用Package

CREATE OR REPLACE PACKAGE EEP.PKG1
            IS
      procedure test_java ();
End PKG1;

CREATE OR REPLACE PACKAGE BODY EEP.PKG1
            IS
      procedure test_java () as language java name ' PKG1.test.main(java.lang.String[])';

 
End PKG1;

3.執行 execute PKG1. test_java ();