oracle調用java – JAVA編程語言程序開發技術文章

其它語言的函數的調用
java函數調用
 
在oracle數據庫建立一個java資源,也可以用loadjava命令裝載其它的java類或者jar
 
create or replace and compile java sourcenamed mytestjava as
public class Factorial {
public static int calcFactorial (int n) {
if (n == 1) return 1;
else return n * calcFactorial (n – 1) ;
}
}
 
建立一個映射函數
CREATE OR REPLACE FUNCTION plstojavafac_fun
(N NUMBER)
RETURN NUMBER
AS
LANGUAGE JAVA
NAME 'Factorial.calcFactorial (int) return int';
 
selectplstojavafac_fun(4) from dual
—-
24
 
這是一個極其簡單的例子,但是有瞭這樣的功能,你可以調用外部的任何外部命令,也可以與其他任何外部數據庫數據文件進行通訊瞭
 
 
下面一個例子,實現在數據庫內調用任何外部命令的功能
 

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS 
import java.io.*; 
public class Host { 
  public static void executeCommand(String command) { 
    try { 
      String[] finalCommand; 
      if (isWindows()) { 
        finalCommand = new String[4]; 
        // Use the appropriate path for your windows version. 
        finalCommand[0] = "C:\\windows\\system32\\cmd.exe";  // Windows XP/2003 
        //finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";  // Windows NT/2000 
        finalCommand[1] = "/y"; 
        finalCommand[2] = "/c"; 
        finalCommand[3] = command; 
      } 
      else { 
        finalCommand = new String[3]; 
        finalCommand[0] = "/bin/sh"; 
        finalCommand[1] = "-c"; 
        finalCommand[2] = command; 
      } 
   
      final Process pr = Runtime.getRuntime().exec(finalCommand); 
      pr.waitFor(); 
      new Thread(new Runnable(){ 
        public void run() { 
          BufferedReader br_in = null; 
          try { 
            br_in = new BufferedReader(new InputStreamReader(pr.getInputStream())); 
            String buff = null; 
            while ((buff = br_in.readLine()) != null) { 
              System.out.println("Process out :" + buff); 
              try {Thread.sleep(100); } catch(Exception e) {} 
            } 
            br_in.close(); 
          } 
          catch (IOException ioe) { 
            System.out.println("Exception caught printing process output."); 
            ioe.printStackTrace(); 
          } 
          finally { 
            try { 
              br_in.close(); 
            } catch (Exception ex) {} 
          } 
        } 
      }).start(); 
   
      new Thread(new Runnable(){ 
        public void run() { 
          BufferedReader br_err = null; 
          try { 
            br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream())); 
            String buff = null; 
            while ((buff = br_err.readLine()) != null) { 
              System.out.println("Process err :" + buff); 
              try {Thread.sleep(100); } catch(Exception e) {} 
            } 
            br_err.close(); 
          } 
          catch (IOException ioe) { 
            System.out.println("Exception caught printing process error."); 
            ioe.printStackTrace(); 
          } 
          finally { 
            try { 
              br_err.close(); 
            } catch (Exception ex) {} 
          } 
        } 
      }).start(); 
    } 
    catch (Exception ex) { 
      System.out.println(ex.getLocalizedMessage()); 
    } 
  } 
   
  public static boolean isWindows() { 
    if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) 
      return true; 
    else 
      return false; 
  } 
}; 

www.aiwalls.com
 
 
在調用外部程序之前,必須授權給數據庫用戶相應的權限
 
— Created on 2007-9-13 by Tiwen
declare
begin
DBMS_JAVA.grant_permission('TIWEN','java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute,delete');
Dbms_Java.Grant_Permission('TIWEN','SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
Dbms_Java.Grant_Permission('TIWEN','SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
end;
 
建立映射過程
CREATE OR REPLACE PROCEDUREhost_command (p_command  IN  VARCHAR2)
AS LANGUAGEJAVA
NAME'Host.executeCommand (java.lang.String)';
 
測試
DECLARE
 l_output DBMS_OUTPUT.chararr;
 l_lines  INTEGER := 1000;
BEGIN
 DBMS_OUTPUT.enable(1000000);
 DBMS_JAVA.set_output(1000000);
 host_command('dir g:\');  –執行顯示目錄的命令
 DBMS_OUTPUT.get_lines(l_output, l_lines);
 FOR i IN 1 .. l_lines LOOP
   DBMS_OUTPUT.put_line(l_output(i));
    NULL;
  END LOOP;
END;

摘自 田文的專欄

發佈留言