Execute a shell script with a trigger
Olivier Marcou
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
Thomas Kyte
(if that email address didn't require changing)
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
tk...@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.
Reinhard Wahl
I read this thread and I tried running the scripts on HP-UX via
Exceed-Terminal on a NT-PC and the only effect is that my windows are
always closing, but tmp.csh was written. I have no experience with
C-Shell. What's wrong?
Reinhard
Oracle-Release 7.3.4.0.0
PL/SQL Release 2.3.4.0.0
Thomas Kyte schrieb:
--
MfG R.Wahl (ZV-807)
----------------------------------------------------
E-Mail@work: mailto:wa...@zv.fhg.de
E-Mail@home: mailto:Reinha...@online.de
----------------------------------------------------
Website http://www.online.de/homepages/ReinhardWahl
----------------------------------------------------
Hiermit widerspreche ich der Nutzung oder
Uebermittlung meiner Daten fuer Werbezwecke
oder fuer die Markt- oder Meinungsforschung
gemaess Par. 28 Abs. 3 Bundesdatenschutzgesetz.
----------------------------------------------------
Please do not use my E-Mail address for advertising!
----------------------------------------------------
Stephen Harris
[ snip ]
As I said to Thomas in private email, Unix daemons are not so easy to do.
What he posted was clever hackup code, suitable for debugging, and very
good demonstration of using dbms_pipe. However, such code is far from
being production quality and is full of potential gotcha's.
It's unfortunate that Oracle 7 forces you to such lengths, but if you do
want to go down this path, then please spend a lot of care on the Unix daemon
side of things. Treat Thomas's code as a guideline. Your database will love
you, in the long run :-)
--
rgds
Stephen