Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: System calls from PL/SQL stored procedures

Re: System calls from PL/SQL stored procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/09/23
Message-ID: <36095156.4161063@192.86.155.100>#1/1

A copy of this was sent to Richard Allard <rallard_at_commserv.ucsb.edu> (if that email address didn't require changing) On Wed, 23 Sep 1998 11:54:15 -0700, you wrote:

>I would like to set up web pages (generated by PL/SQL packages) with
>forms that will pass parameters to SQLLOADER, IMP, and EXP. I know that
>I can use DBMS_PIPE on the PL/SQL side to send the commands, but I need
>a daemon that will receive the commands and issue the system call. I do
>not have "Programmer/2000" (Pro*C), so I need a program that can listen
>and send through a named pipe, without the Pro*C libraries. I've
>written mountains of C, but never a daemon. Does anyone have C source
>(preferable gcc) for a daemon to do this kind of thing, that they are
>willing to share?
>
>Thanks.

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 (instead of using the PLEX binary, use this shell script make sure it is named host.csh)

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

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 Wed Sep 23 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US