Re: HELP: system calls

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 26 May 1998 13:38:31 GMT
Message-ID: <356ac501.4781114@192.86.155.100>

A copy of this was sent to callagha_at_cs.fsu.edu (Geoffrey Callaghan) (if that email address didn't require changing) On 22 May 1998 19:28:35 GMT, you wrote:

>
>Is there a way to make a system call from a PL/SQL stored procedure? I'm
>trying to launch an FTP from a UNIX box that is running my Oracle
>application. Any help would be greatly appreciated.
>
> Geoff Callaghan
> callagha_at_cs.fsu.edu

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, make sure the directory you put this in has 'write' permissions)

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

Caveats:

its able to run any host command you want (and others you don't). You probably want to rewrite it to execute only the commands you are interested in.

its non-transactional -- if you rollback the host command has already executed or is executing.

its asyncronous -- the host command starts going and you get control back right away. you could rewrite to be a blocking call and return the results.

you must grant execute on dbms_pipe directly (not via a role) to the owner of the host procedure or it will not compile.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue May 26 1998 - 08:38:31 CDT