In Oracle7 -- you cannot execute another program from PLSQL. You can 'talk' to an already running program (which in turn can execute another process). See </code> http://asktom.oracle.com/~tkyte/plex/index.html <code>for an idea on how this works -- we can use dbms_pipe to send messages to another session and have it do something for us.

In Oracle8 -- this feature is called an external procedure and is quite easy to implement. You would code your stored procedure in C, the C can use a system() command for example to run another process.

In Oracle8i, release 8.1 -- a java stored procedure can run external processes as well without having to code C.


In unix, you can do something like this as well (make sure you understand the security implications here -- run host.csh as a very Non-priveleged user!)

This is a quick and dirty daemon -- written in csh (the cool shell)..

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 (use this shell script make sure it is named host.csh)

-------------------- 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).....