oracle觸發器調用java程式

標籤：led   blog   rman   jdbc   byte   賦權   java.net   close   data   

1、建立java程式：接收預存程序傳遞的參數，發送socket通訊。  create or replace and compilejava source named jym asimport java.io.BufferedReader;import java.io.InputStream;importjava.io.InputStreamReader;import java.io.OutputStream;import java.io.StringWriter;importjava.net.InetSocketAddress;import java.net.Socket;import java.sql.Connection;import java.sql.DriverManager;importjava.sql.PreparedStatement;import java.sql.Statement;public class JYM {public static  StringsendSynMsg(String ipAddr, byte[] datas) throws Exception{  InetSocketAddress endpoint = new InetSocketAddress(ipAddr ,18002);  Socket socket = null;  OutputStream out = null;  InputStream in = null;  try {   socket = new Socket();   socket.connect(endpoint);   out = socket.getOutputStream();   in = socket.getInputStream();   out.write(datas);   out.flush();   return null;  } finally {   if (out != null) {    try {     out.close();    } catch(Exception ex) {     ex.printStackTrace();    }   }   if (in != null) {    try {     in.close();    } catch(Exception ex) {     ex.printStackTrace();    }   }   if (socket != null) {    try {     socket.close();    } catch(Exception ex) {     ex.printStackTrace();    }   }  }}public static void say(String ip,byte[] context) throws Exception {  String str=JYM.sendSynMsg(ip,context);    Class.forName("oracle.jdbc.driver.OracleDriver");  Connection conn =DriverManager.getConnection("jdbc:oracle:thin:@192.168.9.109:1521:orcl","zhym","jhhz621");   PreparedStatement pr=conn.prepareStatement("insert intobackdata(context) values(?)");  pr.setBytes(1, context);  pr.executeUpdate();  pr.close();  conn.close();}}  2、預存程序調用java source create or replace procedure socket(   ip varchar2,   context raw   )   as  language java name  ‘JYM.say(java.lang.String,byte[])‘;   3、觸發器調用預存程序 create or replace trigger todata_after     after insert on todata     for each row   begin      socket(‘192.168.9.12‘,:new.context);   end todata_after;   代碼寫好後可以先用預存程序測試：Sql代碼  call  socket(‘192.168.9.12‘,‘023132‘);    為了能在控制台中看見錯誤資訊，要先運行下面3句話  set serveroutput on;   set serveroutput on size 5000;   call dbms_java.set_output(5000);  假如你運行預存程序時爆出了  請給使用者複製許可權的提示則按照提示在sysdba 下賦許可權，如：Sql代碼  exec dbms_java.grant_permission(‘zhym‘,‘SYS:java.net.SocketPermission‘,‘192.168.9.12:18002‘,‘connect,resolve‘)     exec dbms_java.grant_permission(‘ZHYM‘,‘SYS:java.net.SocketPermission‘,‘127.0.0.1:1521‘,‘connect,resolve‘)  execdbms_java.grant_permission(‘zhym‘,‘SYS:java.net.SocketPermission‘,‘192.168.9.12:18002‘,‘connect,resolve‘) execdbms_java.grant_permission(‘ZHYM‘,‘SYS:java.net.SocketPermission‘,‘127.0.0.1:1521‘,‘connect,resolve‘) exec 後面的語句會在你的報錯提示中列印出來，只要前面加exec就可以了。

 

oracle觸發器調用java程式