Re: Call Shell Script from Stored Proc

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/04
Message-ID: <340f2be6.3991249_at_newshost>#1/1

On 3 Sep 1997 20:24:48 GMT, eriche_at_gr.hp.spamsux.com (Eric Herbert) wrote:

>Here I go again:
>I was wondering if someone could point me towards some help.
>
>Specifically:
>
>My user fires up his form. He updates some data in one of the tables.
>His form has a button that calls a stored procedure on the (of course)
>server that massages some data and creates a text file on the server
>using the UTL_FILE built in. All of this is fine and dandy.
>
>NOW- we need the stored procedure to issue a command to the UX
>operating system that will launch a shell script that ftp's this
>text file to a different server. How can we call a shell script
>from a stored procedure, or even off a client form for that matter?
>Any ideas?
>

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 (instead of using the PLEX binary, use this shell script)

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

Its up to you to 'secure' it (to make sure no one can send an 'rm -rf *' for example).....

>I seem to remember that this is impossible, but it seems like
>it would be needed by so many people there must be a way.
>
>TIA,
>air
>ick
>
>ps- please note I crossposted this. Send followups to the appropriate
>newsgroup only.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 04 1997 - 00:00:00 CEST