1,创建java source
create or replace and compile java source named Execshellcmd as
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
public class Execshellcmd {
public static String callShell(String path) {
String message = "";
try {
Process process = Runtime.getRuntime().exec(path);
int waitFor = process.waitFor();
InputStream is = process.getErrorStream();
InputStreamReader isr = new InputStreamReader(is);
BufferedReader br = new BufferedReader(isr);
String line = null;
while ((line = br.readLine()) != null) {
message += line;
}
message += " Process waitFor=" + waitFor;
int exitValue = process.exitValue();
message += " Process exitValue=" + exitValue;
} catch (Exception e) {
message = e.getMessage();
e.printStackTrace();
}
return message;
}
}
2, 创建存储过程或者函数进行调用
create or replace function fun_callshell(p_command STRING) return varchar2
AS LANGUAGE JAVA
NAME'Execshellcmd.callShell (java.lang.String) return java.lang.String';
create or replace procedure pro_callshell(p_command STRING)
IS LANGUAGE JAVA
NAME'Execshellcmd.callShell (java.lang.String)';
3,赋权
begin
dbms_java.grant_permission('LOGE','SYS:java.io.FilePermission','<<ALL FILES>>','read,write,execute,delete');
end;
begin
dbms_java.grant_permission('LOGE','java.lang.RuntimePermission','*','writeFileDescriptor' );
end;
4,测试
select fun_callshell('/home/oracle/1.sh') from dual;
第二种 使用scheduler
测试sys用户执行,
begin
DBMS_SCHEDULER.create_job (job_name => 'test', job_type => 'EXECUTABLE', job_action => '/home/oracle/1.sh');
end;
或者带有执行间隔的:
dbms_scheduler.create_job(job_name=>'j_test',job_type=>'executable',job_action=>'/home/oracle/1.sh',start_date=>sysdate,repeat_interval=>'freq=daily;interval=1',number_of_arguments=>1);
执行:
begin
DBMS_SCHEDULER.run_job (job_name => 'test');
end;