Re: Running Unix Commands from within Oracle

If you're interested in doing this from Perl, there are example scripts at www.cybcon.com/~jkstill

This allows *much* greater control and flexibility than a shell script, and much less work than C.

Jared

On Wednesday 12 September 2001 02:45, Thomas, Kevin wrote:
> All,
>
> A few requests have come in for code to be able to run Unix commands from
> within PL/SQL, so here it is, apologies for the length of the post, I never
> know whether attachment work or not.
>
> It's basically divided into three seperate parts, 1 unix shell script, 1
> unix c shell script (I think!) and 2 stored procedures.
>
> In order to work you need to create a pipe within Oracle called
> "message_pipe".
>
> Script 1 - Unix shell script, starts up the c shell script if it's not
> already running.
> --------
>
> #!/bin/ksh
> ###########################################################################
># ###
> # host_startup.sh
> #
> # K. Thomas August 2001
> #
> # Purpose : Starts up the HOST.CSH file for the SMUT tool if
> # : the file is not already running
> #
> # Start This Off by:
> # Kicked off by CRON at 7:45am every morning
> #
> # Date Version Author Log Description
> #
> ---------------------------------------------------------------------------
>- -
> #
> #
> ###########################################################################
># ####
>
> ps -fe | grep -i host.csh | grep -v grep > host_startup.tmp
> if [ ! -s host_startup.tmp ]; then
> date > host_run.log
> nohup host.csh & > host_run.log
> else
> echo "SMUT Unix Host already running" > host_run.log
> fi
> rm host_startup.tmp
>
>
> Script 2 - Unix C shell script, once launched this "polls" the message pipe
> within oracle looking for instructions on what unix command to run. It
> basically accesses Oracle, gets the info off the pipe and build another
> Unix shell script on the fly and then executes that shell script, once
> executed the C shell script re-runs itself so that it is constantly waiting
> for something.
> --------
> #!/bin/csh -f
>
> ###########################################################################
># ###
> # host.csh
> #
> # K. Thomas August 2001
> #
> # Purpose : Runs in background continually polling the HOST_PIPE for info
> # : coming through from the Oracle process.
> #
> # Start This Off by:
> # Kicked off by the host_startup.sh which is run at 7:45am each morning
> #
> # Date Version Author Log Description
> #
> ---------------------------------------------------------------------------
>- -
> #
> #
> ###########################################################################
># ####
> sqlplus dbv/dbv_at_t_nhspint <<"EOF" | grep '^#' | sed 's/^.//' > tmp.csh
>
> set serveroutput on
> set linesize 300
>
> 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( '#LOGDATE=`date +''%d/%m/%Y
> %H:%M:%S''`' );
> dbms_output.put_line( '#echo "$LOGDATE [ ' || command || '
> ]" >> host.log' );
> dbms_output.put_line( '#if [ ! -r SHUTDOWN ] ' );
> dbms_output.put_line( '#then' );
> dbms_output.put_line( '# exec host.csh' );
> dbms_output.put_line( '#else' );
> dbms_output.put_line( '# rm -f SHUTDOWN' );
> dbms_output.put_line( '#fi' );
> end if;
> end;
> /
> spool off
> "EOF"
>
> Script 3 - This is the stored procedure that accepts the unix commands and
> places them on the message_pipe to be picked up by the previous script.
> Essentially you run this script like so: sql> exec host('ls -altr');
> --------
> /*
>
> |==========================================================================
> |=
>
> ====
>
> || host.sql
> ||
> || K. Thomas August 2001
> ||
> || Purpose : Takes in the unix command as a parameter and adds it to the
> ||
> || : pipe stack.
> ||
> || Issue the command via: sql> exec host( '<unix command>' );
> || e.g. : sql> exec host( 'ps -ef | grep agtmgt' );
> ||
> || Date Version Author Log Description
>
> ---------------------------------------------------------------------------
>- -
>
> ||=========================================================================
> ||=
>
> =====
> */
> 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;
> /
>
> Script 4 - This stored procedure allows you to "flush" the pipe to avoid
> spurrious messages being sent through the pipe. It's advisable to run this
> *before* starting up the shell script.
> --------
> /*
>
> |==========================================================================
> |=
>
> ====
>
> || flush_host_pipe.sql
> ||
> || K. Thomas August 2001
> ||
> || Purpose : Flushes messages from the pipe stack.
> ||
> || Issue the command via: sql> exec flush_host_pipe;
> ||
> || Date Version Author Log Description
>
> ---------------------------------------------------------------------------
>- -
>
> ||=========================================================================
> ||=
>
> =====
> */
> procedure flush_host_pipe
> as
> begin
> dbms_pipe.purge( 'HOST_PIPE' );
> end;
>
> -----Original Message-----
> Sent: 11 September 2001 16:25
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Kev,
>
> Mind just posting the code to the list? I'm sure there will be many
> responses to yourself asking for this code, so it may be easier just to
> post it directly..
>
> Cheers
>
> Mark
>
> -----Original Message-----
> Kevin
> Sent: Tuesday, September 11, 2001 15:00
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Roland,
>
> Yes it is possible, I have got a piece of code that I got from the site
> asktom.oracle.com, that allows you to send Unix commands to the shell from
> within SQL. I'm making the assumption that it's a Unix environment you want
> to make the call within?
>
> If it is, give me a shout and I'll let you have the code...
>
> Regards,
> Kev.
>
>
> __________________
>
> Kevin Thomas
> Technical Analyst
> Deregulation Services
> Calanais Ltd.
> (2nd Floor East - Weirs Building)
> Tel: 0141 568 2377
> Fax: 0141 568 2366
> http://www.calanais.com
>
>
> -----Original Message-----
> Sent: 11 September 2001 13:10
> To: Multiple recipients of list ORACLE-L
>
>
> Hallo,
>
> Is it possible to do ftp commands directly from PL/SQL procedur? Including
> logon to the ftp server.?
> Give me an example please if possible.
>
>
> Thanks in advance
>
> Roland
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Roland.Skoldblom_at_ica.se
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Thomas, Kevin
> INET: Kevin.Thomas_at_calanais.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).