我的项目中需要将数据库中新增数据实时推送到客户端,首先我用java写的网络客户端程序以及存储过程,然后新增数据时触发器调用存储过程与服务器建立连接并发送新增数据,java sources和procedures都编译通过,insert时报错如下:
SQL> insert into lava.blacklist(color) values('white');
insert into lava.blacklist(color) values('white')
*
第 1 行出现错误:
ORA-29532: Java 调用被未捕获的 Java 异常错误终止:
java.security.AccessControlException: the Permission (java.net.SocketPermission
127.0.0.1:9090 connect,resolve) has not been granted to LAVA. The PL/SQL to
grant this is dbms_java.grant_permission( 'LAVA',
'SYS:java.net.SocketPermission', '127.0.0.1:9090', 'connect,resolve' )
ORA-06512: 在 "LAVA.SEND_MESSAGE", line 1
ORA-06512: 在 "LAVA.BL_TB_TRI", line 5
ORA-04088: 触发器 'LAVA.BL_TB_TRI' 执行过程中出错用户是lava,被赋予可执行该存储过程的权限java sources如下:
create or replace and compile java source named lava.client as
import java.io.*;
import java.net.*;public class Client {
static Socket socket;
static PrintWriter out;
public static void start() {
try {
socket = new Socket(InetAddress.getByName(null), 9090);
out = new PrintWriter(
new BufferedWriter(
new OutputStreamWriter(
socket.getOutputStream())), true);
out.print("hello from oracle");
} catch (IOException e) {}
finally {
try {
if(out != null)out.close();
if(socket != null)socket.close();
out = null;
socket = null;
} catch (IOException e) {}
}
}
}procedures如下:
create or replace procedure lava.send_message
as language java name
'Client.start()';触发器如下:
create or replace trigger LAVA.bl_tb_tri
before insert on lava.blacklist
for each row
declare
-- local variables here
begin
select bl_tb_seq.nextval into :new.id from dual;
lava.send_message();
end bl_tb_tri;
SQL> insert into lava.blacklist(color) values('white');
insert into lava.blacklist(color) values('white')
*
第 1 行出现错误:
ORA-29532: Java 调用被未捕获的 Java 异常错误终止:
java.security.AccessControlException: the Permission (java.net.SocketPermission
127.0.0.1:9090 connect,resolve) has not been granted to LAVA. The PL/SQL to
grant this is dbms_java.grant_permission( 'LAVA',
'SYS:java.net.SocketPermission', '127.0.0.1:9090', 'connect,resolve' )
ORA-06512: 在 "LAVA.SEND_MESSAGE", line 1
ORA-06512: 在 "LAVA.BL_TB_TRI", line 5
ORA-04088: 触发器 'LAVA.BL_TB_TRI' 执行过程中出错用户是lava,被赋予可执行该存储过程的权限java sources如下:
create or replace and compile java source named lava.client as
import java.io.*;
import java.net.*;public class Client {
static Socket socket;
static PrintWriter out;
public static void start() {
try {
socket = new Socket(InetAddress.getByName(null), 9090);
out = new PrintWriter(
new BufferedWriter(
new OutputStreamWriter(
socket.getOutputStream())), true);
out.print("hello from oracle");
} catch (IOException e) {}
finally {
try {
if(out != null)out.close();
if(socket != null)socket.close();
out = null;
socket = null;
} catch (IOException e) {}
}
}
}procedures如下:
create or replace procedure lava.send_message
as language java name
'Client.start()';触发器如下:
create or replace trigger LAVA.bl_tb_tri
before insert on lava.blacklist
for each row
declare
-- local variables here
begin
select bl_tb_seq.nextval into :new.id from dual;
lava.send_message();
end bl_tb_tri;
我是第一次用oracle,创建用户的时候把所用权限都赋给lava用户了,也没有注意里面是否有socket,应该包含了吧
dbms_java.grant_permission( 'LAVA','SYS:java.net.SocketPermission', '127.0.0.1:9090', 'connect,resolve' )