Question

用Java代码创建Oracle触发器要怎么实现

Answer 1

1、Java代码创建Oracle触发器

Java代码如下：  

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();   

}   

}  

create or replace and compilejava source named jym as

import 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

Sql代码  

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

Sql代码  

总结：假如运行存储过程时爆出了  请给用户复制权限的提示则按照提示在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就可以了。

Answer 2

用Java代码创建Oracle触发器要怎么实现
create or replace and compile java source named TestClass as
public class TestClass
{
public static String getName(String name) {
String name = "xoxo";
return name " with TTTT";
}
}
然后在触发器中调用：
create or replace trigger TRI_TEST before delete or update or insert on TABLENAME for each row
declare name varchar2(20);
begin
if inserting then
FUNCTION getName( who VARCHAR2 ) RETURN VARCHAR2 IS LANGUAGE JAVA NAME TestClass.getName(java.lang.String) return String';
end if;
end;

Answer 3

首先将java代码放在数据库里：例如
create or replace and compile java source named TestClass as
public class TestClass
{
public static String getName(String name) {
String name = "xoxo";
return name " with TTTT";
}
}
然后在触发器中调用：
create or replace trigger TRI_TEST before delete or update or insert on TABLENAME for each row
declare name varchar2(20);
begin
if inserting then
FUNCTION getName( who VARCHAR2 ) RETURN VARCHAR2 IS LANGUAGE JAVA NAME TestClass.getName(java.lang.String) return String';
end if;
end;

Answer 4

create or replace and compile java source named TestClass as
public class TestClass
{
public static String getName(String name) {
String name = "xoxo";
return name " with TTTT";
}
}
然后在触发器中调用：
create or replace trigger TRI_TEST before delete or update or insert on TABLENAME for each row
declare name varchar2(20);
begin
if inserting then
FUNCTION getName( who VARCHAR2 ) RETURN VARCHAR2 IS LANGUAGE JAVA NAME TestClass.getName(java.lang.String) return String';
end if;
end;