execute shell script from stored procedure

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 < 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.
[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18921899/viewspace-1017651/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18921899/viewspace-1017651/