oracle触发器调用java程序

1、创建java程序：接收存储过程传递的参数，发送socket通信。

create or replace and compile java source named jym as
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.StringWriter;
import java.net.InetSocketAddress;
import java.net.Socket;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class JYM {
	public static  String sendSynMsg(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 into backdata(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;

 

 

代码写好后可以先用存储过程测试：

call  socket('192.168.9.12','023132');

 为了能在控制台中看见错误信息，要先运行下面3句话

set serveroutput on;
set serveroutput on size 5000;
call dbms_java.set_output(5000);

 

 

假如你运行存储过程时爆出了  请给用户复制权限的提示则按照提示在sysdba 下赋权限，如：

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')

 

exec 后面的语句会在你的报错提示中打印出来，只要前面加exec就可以了。