Re: Execute a shell script with a trigger

From: Reinhard Wahl <wahl_at_zv.fhg.de>
Date: 1998/12/15
Message-ID: <3676292C.311FA8BE_at_zv.fhg.de>#1/1


Hi Thomas,
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:
>
> 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.

-- 
MfG R.Wahl (ZV-807)
----------------------------------------------------
E-Mail_at_work: mailto:wahl_at_zv.fhg.de
E-Mail_at_home: mailto:ReinhardWahl_at_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!
----------------------------------------------------
Received on Tue Dec 15 1998 - 00:00:00 CET

Original text of this message