Re: Execute a shell script with a trigger
Date: 1998/12/14
Message-ID: <36798c29.15159938_at_192.86.155.100>#1/1
[Quoted] A copy of this was sent to "Olivier Marcou" <omarcou_at_worldnet.fr> (if that email address didn't require changing) On Mon, 14 Dec 1998 21:24:21 +0100, you wrote:
>Hello,
>
>Maybye, someone can help me?
>
>I've got a shell script which must be execute only when a column of a table
>was updated.
>
>So, is it possible to execute this script by a trigger on this table?
>
>If someone have a solution, thanks.
>
>Olivier
>
>Oracle v7.3.4
>
In 7.3.4, you can use dbms_pipes to do this.
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)
- bof ---------------------------- #!/bin/csh -f
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 Service Industries
Reston, VA USA
-- 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 Mon Dec 14 1998 - 00:00:00 CET