Oracle调用shell
JAVA存储过程调用 LINUX SHELL 问题
create or replace and compile java source named util as
import java.io.*;
import java.lang.*;
import oracle.sql.*;
public class Util extends Object {
public static String runcmd(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(args);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(),bufSize);
int len;
byte buffer[] = new byte[bufSize];
while((len = bis.read(buffer,0,bufSize)) != -1 )
{
System.out.write(buffer,0,len);
}
rc = p.waitFor();
}
catch(Exception e)
{
//return e.toString();
e.printStackTrace();
args = "test";
//return "E.PRINTSTACKTRACE";
//return args;
//rc = -2;
}
finally
{
return args;
}
}
}
create or replace function RUN_CMD(p_cmd in varchar2) return varchar2 as
language java
name 'Util.runcmd(java.lang.String)return java.lang.String';
create or replace procedure rc(p_cmd in varchar2)
is
x varchar2(255) := null;
begin
x := run_cmd(p_cmd);
dbms_output.put_line(x);
end rc;
begin
dbms_java.grant_permission('TEST','java.io.FilePermission','/home/oracle/test_ja
va.sh', 'execute');
dbms_java.grant_permission('TEST','java.io.FilePermission','/bin/sh', 'execute');
dbms_java.grant_permission('TEST', 'java.lang.RuntimePermission', '*', 'writeFileDescriptor');
end;
exec dbms_java.grant_permission( 'TEST', 'SYS:java.io.FilePermission', '<
>', 'execute' );
然后 exec rc('/bin/sh /home/oracle/test_java.sh');
--==========================================
$ cat >/tmp/test111.sh < /tmp/test111.out
EOF
$ chmod +x /tmp/test111.sh
$ sqlplus system
SQL> begin
dbms_scheduler.create_job(
job_name=>'TEST_OS_EXEC_111',
job_type=>'executable',
job_action=>'/tmp/test111.sh',
enabled=>TRUE
);
end;
/
2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL> select status from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='TEST_OS_EXEC_111';
STATUS
------------------------------
SUCCEEDED
BEGIN
2 dbms_scheduler.create_job(
3 job_name => 'LOGSHIP',
4 job_type => 'EXECUTABLE',
5 job_action => 'C:\scripts\logshipvbs.vbs');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> execute DBMS_SCHEDULER.run_job ('LOGSHIP') BEGIN DBMS_SCHEDULER.run_job ('LOGSHIP'); END;
--==================================================== Ask tom:
I can think of a couple of different ways....
o In Oracle8i, release 8.1, we could use java to run a system command with an "&"
after
it (assuming unix) or perhaps "start xxx.cmd" if using NT.
o In Oracle8.0 and up, we can write an external procedure in C that runs host commands
with system() and the "&". (see attached for an external procedure example)
o In Oracle7.0 and up, we can use dbms_pipes to talk to a daemon running outside the database. Here is a simple example that uses sqlplus to be the daemon:
A quick and dirty way to do this is with a csh script and sqlplus as such (cut and pasted from another email)
Ok, so can you do this without C? Yes. Here is a PL/SQL subroutine you can install in your schema:
create or replace procedure host( cmd in varchar2 )
as
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
/
Here is a C-Shell script you can run in the background, it should be named host.csh. host.csh will be run by you after the db is up. it will create temporary scripts "tmp.csh" that it will run. the last thing these tmp.csh scripts do is re-run host.csh
to get the next request....
-------------------- bof ----------------------------
#!/bin/csh -f
sqlplus tkyte/tkyte <<"EOF" | grep '^#' | sed 's/^.//' > tmp.csh
set serveroutput on
declare
status number;
command varchar2(255);
begin
status := dbms_pipe.receive_message( 'HOST_PIPE' );
if ( status <> 0 ) then
dbms_output.put_line( '#exit' );
else
dbms_pipe.unpack_message( command );
dbms_output.put_line( '##!/bin/csh -f' );
dbms_output.put_line( '#' || command );
dbms_output.put_line( '#exec host.csh' );
end if;
end;
/
spool off
"EOF"
chmod +x tmp.csh
exec tmp.csh
----------------------- EOF ---------------------------------
If you run this in the background (The script), you'll be able to have it execute any host command you want. Run this in one window for example and in anther window go into sql*plus and try:
SQL> exec host( 'ls -l' );
SQL> exec host( 'uptime' );
SQL> exec host( 'echo Hello World' );
SQL> exec host( 'exit' );
You'll see the output of ls -l, uptime, and echo happen on the other window where the shell script is running (shows you a way to debug pl/sql routines, use "host( echo some string )" and you'll get real time feedback from your pl/sql procedure).....
Make sure you understand the ramifications of the above. It does absolutely no checking
anywhere that only valid commands are executed. If you run this as the oracle account and someone sends "rm -rf *" -- watch out. This is an example -- it needs to be more robust.
浙公网安备 33021202002483