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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Running Unix Commands from within Oracle

RE: Running Unix Commands from within Oracle

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Wed, 12 Sep 2001 03:18:22 -0700
Message-ID: <F001.0038B23B.20010912033047@fatcity.com>

Cheers!

Mark

-----Original Message-----
Kevin
Sent: Wednesday, September 12, 2001 10:45 To: Multiple recipients of list ORACLE-L

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: Mark Leith
  INET: mark_at_cool-tools.co.uk

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: Mark Leith
  INET: mark_at_cool-tools.co.uk

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).
Received on Wed Sep 12 2001 - 05:18:22 CDT

Original text of this message

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