说明:
通过调用存储过程传入一个shell命令,然后执行命令。
存储过程调用java代码,java代码执行shell命令
1.编写 java 代码 ,文件名 A.java
import java.io.BufferedReader;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.UnknownHostException;
public class A
{
public static String list_cmd(String str){
Runtime runtime=Runtime.getRuntime();
StringBuffer enco = new StringBuffer();
enco.append("utf8");
try{
Process proc =runtime.exec(str);
InputStream inp_suc=proc.getInputStream();
InputStream inp_err=proc.getErrorStream();
BufferedReader bfr_err = new BufferedReader(new InputStreamReader(inp_err,enco.toString()));
BufferedReader bfr_suc = new BufferedReader(new InputStreamReader(inp_suc,enco.toString()));
String strLine;
while( (strLine=(bfr_suc.readLine())) != null){
System.out.println(strLine);
}
while( (strLine=(bfr_err.readLine())) != null){
System.out.println(strLine);
}
proc.destroy();
inp_suc.close();
inp_err.close();
}catch (Exception e) {
System.out.println("EXECUTE IS ERROR!");
System.out.println(e.getMessage());
}
return "";
}
public static void main(String[] args){
list_cmd(args[0]);
}
}
2.测试 java 代码,显示出当前用户名即代码正确。
javac A.java
java A whoami
3.创建Oracle 用户
SQL> create user ps identified by 123;
SQL> grant dba to ps;
4.将java 类改装成创建javasource语法,(将测试的main方法去掉,在第一行添加 create or replace and compile java source named exe_linux_shell as)
create or replace and compile java source named exe_linux_shell as
import java.io.BufferedReader;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.UnknownHostException;
public class A
{
public static String list_cmd(String str){
Runtime runtime=Runtime.getRuntime();
StringBuffer enco = new StringBuffer();
enco.append("utf8");
try{
Process proc =runtime.exec(str);
InputStream inp_suc=proc.getInputStream();
InputStream inp_err=proc.getErrorStream();
BufferedReader bfr_err = new BufferedReader(new InputStreamReader(inp_err,enco.toString()));
BufferedReader bfr_suc = new BufferedReader(new InputStreamReader(inp_suc,enco.toString()));
String strLine;
while( (strLine=(bfr_suc.readLine())) != null){
System.out.println(strLine);
}
while( (strLine=(bfr_err.readLine())) != null){
System.out.println(strLine);
}
proc.destroy();
inp_suc.close();
inp_err.close();
}catch (Exception e) {
System.out.println("EXECUTE IS ERROR!");
System.out.println(e.getMessage());
}
return "";
}
}
5. 创建存储过程
create or replace procedure p_exe_linux(str varchar2) as language java
name 'A.list_cmd(java.lang.String)';
/
6. 授权
exec dbms_java.grant_permission('C##ORCL', 'SYS:java.io.FilePermission', '<>', 'execute' )
7.如果在SQLplus 上想看到执行shell 命令的结果:
SQL> SET SERVEROUTPUT ON --打开服务器输出
SQL> exec dbms_java.set_output(11); --打开java 输出
8.执行
EXEC P_EXE_LINUX('ls -l')
8.使用例子:
执行Oracle expdp 命令
SQL> exec p_exe_linux('expdp 用户名/密码 dumpfile=数据文件名.dmp logfile=日志文件名.log tables=导出的表名')
遇到问题:
1. ORA-29536: badly formed source: oracle.aurora.sqljdecl.TokenMgrError: Lexical error at line 8, column 1. Encountered: "\\ufffd" (65533), after : ""
java 代码中有特殊字符,如:\t .去掉特殊字符,把 \t 改成 空格
2. ORA-00955: name is already used by an existing object
java 类删除/创建 删除java source提示上面信息,换一个用户
3. 删除 java 类
drop java class “类名”;
drop java source “source名”;