Oracle觸發器反向調用Java程式

標籤：close   rmi   type   檔案   static   int   tco   types   版本   

匯入jar包 在oracle中匯入需要的jar包，我們把編輯好的java類打成jar包，直接在oarcle裡面寫簡單的調用就可以了， 　１、作業系統需要擁有支援loadjava命令的jdk。 　２、載入jlha.jar包，到oracle資料庫中。     操作過程：在dos環境下，輸入命令： loadjava   -r   -f   -o   -user   usscares/[email protected]   jlha.jar 注意：jar包要在1.4的環境下編譯，項目右鍵 properties java compiler compoler compliance level 1.4 即可   否則報version 49 類似的錯誤 調用JAVA類oracle調用JAVA類的方法主要有以下三種：用loadjava方法裝載；可能是調試方便，據說這種方法比較通用。c:\test\hello.javapublic class hello{    public static void main(String[] args)    {        System.out.println("Hello");        hello h = new hello();        h.insertM(9);    }    public static void insertM(int pid)    {        System.out.println("This is the method insertM.");    }} C:\test>loadjava -u test/[email protected] -v -resolve hello.javaSQL> create procedure prc_hehe as language java name ‘hello.main(java.lang.String[])過程已建立。SQL> call prc_hehe();調用完成。SQL> set serveroutput on size 2000SQL> call prc_hehe();調用完成。SQL> exec dbms_java.set_output(2000);PL/SQL 過程已成功完成。SQL> call prc_hehe();HelloThis is the method insertM.調用完成。SQL>show errors;修改java類，先刪除再裝載，方法：dropjava -u test/[email protected] -v -resolve hello.javaloadjava -u test/[email protected] -v -resolve hello.java用sql語句建立create or replace and compile java source named heheASpublic class hello{    public static void msg(String name)    {        System.out.println("hello," + name);    }}; create or replace procedure prc_hehe(       p_name VARCHAR2)aslanguage java name ‘hello.msg(java.lang.String)‘;-- 調用結果SQL> call prc_hehe(‘oopp‘);hello,oopp用外部class檔案來裝載建立create or replace directory CLASS_DIR  as ‘c:\test‘;create or replace java class using bfile(class_dir,‘hello.class‘);create or replace procedure prc_hello(       p_name VARCHAR2)aslanguage java name ‘hello.msg(java.lang.String)‘;-- 測試結果SQL> call prc_hello(‘java‘);java可能出現的錯誤SQL> call prc_hello(‘Jerry‘);call prc_hello(‘Jerry‘)     *第 1 行出現錯誤:ORA-29516: Aurora 宣告失敗: Assertion failure at eox.c:359Uncaught exception System error:   java/lang/UnsupportedClassVersionError原因：機器裝了多個java版本，oracle的java版本低於環境變數設定的版本。解決方案：用$ORACLE_HOME/jdk/javac 重新編譯java檔案核對java已經匯入資料庫select * from user_source where type LIKE ‘JAVA%‘ AND NAME = ‘<java file>‘建立functionCREATE OR REPLACE FUNCTION <FUNCTION_NAME> (<PARAMETER LIST IN ORACLE DATATYPE>) RETURN <ORACLE DATATYPE OF RETURN VARIABLE> AS   LANGUAGE JAVA   NAME ‘<clase.method>(<parameter list in java datatype>) return java datatype of return variable‘; 例：登陸某一使用者登入，並建立java程式資源，在pl/sql中java source中顯示你所編寫的java代碼；create or replace and compile java source named bb_wx_replosssbk asimport java.sql.*;import oracle.jdbc.driver.*;public class bb_wx_replosssbk{    /**    *   社保卡掛失    */    public static String callProc(String sSfzh, String sPwd, String sType)    {        OracleDriver driver = new OracleDriver();        Connection connection = null;        CallableStatement cstmt = null;        String sRtn = "beg";        try        {            sRtn = " try beg";            connection = DriverManager.getConnection("jdbc:oracle:thin:user/[email protected]:1521:orcl");            sRtn = "con";            cstmt = connection.prepareCall("{call run_replosssbk(?,?,?,?)}");            sRtn = "invoke";            cstmt.setString(1, sSfzh);            cstmt.setString(2, sPwd);            cstmt.setString(3, sType);            cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);            sRtn = "set value";            cstmt.executeUpdate();            sRtn = "execute";            sRtn = cstmt.getString(4);        }        catch (Exception e)        {            sRtn = e.toString();            e.printStackTrace();        }        finally        {            try            {                if (cstmt != null)                {                    cstmt.close();                }                if (connection != null)                {                    connection.close();                }            }            catch (Exception e)            {                e.printStackTrace();            }        }        return sRtn;    }}建立調用Java資源的函數create or replace function run_bb_wx_replosssbk(sSfz in varchar2,sPwd in varchar2,sType in varchar2)return varchar2as language java name ‘bb_wx_replosssbk.callProc(java.lang.String,java.lang.String,java.lang.String) return java.lang.String‘;建立一程序呼叫預存程序create or replace procedure RUN(sSfz in varchar2,sPwd in varchar2,sType in varchar2sRtn out varchar2)asbegin--sRtn := run_bb_wx_replosssbk(sSfz in varchar2,sPwd in varchar2,sType in varchar2);Select run_bb_wx_replosssbk(sSfz in varchar2,sPwd in varchar2,sType in varchar2)Into sRtn from dual;end;/附：如果需要java存取檔案，需要使用dba使用者賦權EXEC Dbms_Java.Grant_Permission(‘ONBOARDING‘, ‘SYS:java.lang.RuntimePermission‘, ‘writeFileDescriptor‘, ‘‘);EXEC Dbms_Java.Grant_Permission(‘ONBOARDING‘, ‘SYS:java.lang.RuntimePermission‘, ‘readFileDescriptor‘, ‘‘);EXEC dbms_java.grant_permission( ‘ONBOARDING‘, ‘SYS:java.io.FilePermission‘, ‘<<ALL FILES>>‘, ‘execute‘ );收回許可權的語句如下EXEC Dbms_Java.revoke_Permission(‘ONBOARDING‘, ‘SYS:java.lang.RuntimePermission‘, ‘writeFileDescriptor‘, ‘‘);EXEC Dbms_Java.revoke_Permission(‘ONBOARDING‘, ‘SYS:java.lang.RuntimePermission‘, ‘readFileDescriptor‘, ‘‘);EXEC dbms_java.revoke_permission( ‘ONBOARDING‘, ‘SYS:java.io.FilePermission‘, ‘<<ALL FILES>>‘, ‘execute‘ );

 

Oracle觸發器反向調用Java程式