2013年10月14日 星期一

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 ();