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@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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
