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: Thomas, Kevin <Kevin.Thomas_at_calanais.com>
Date: Wed, 12 Sep 2001 03:45:05 -0700
Message-ID: <F001.0038B283.20010912034531@fatcity.com> 






Good grief!! I can't count, I said divided into 3 seperate parts and then
proceeded to tell you about 4?!? Oh well...grin



K.



"hit any user to continue"






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: 12 September 2001 12:31


To: Multiple recipients of list ORACLE-L




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


Received on Wed Sep 12 2001 - 05:45:05 CDT












Original text of this message













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