Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Scheduling and Oracle Job Through A Trigger

Re: Scheduling and Oracle Job Through A Trigger

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Wed, 30 Sep 1998 18:26:26 +0900
Message-ID: <36120752.706E@bhp.com.au>


Keith Chapman wrote:
>
> Hello,
>
> I would like to run a job on a UNIX system based on the insertion of a
> row onto the database, is this possible? What I would need to do is
> create a trigger and have that trigger call an external program.
>
> Something like
>
> CREATE TRIGGER TEST
> AFTER UPDATE OF TEST_TABLE
> FOR EACH ROW(...)
> WHEN (...)
> BEGIN
> -- RUN EXTERNAL UNIX JOB
> -- (/usr/bin/batchjob)
> END
>
> Is it possible to do this and if so what would be the correct syntax.
>
> Any suggestions would be useful.
>
> Thanks
>
> Keith Chapman

(In 7.3) its not as easy as that....

What you need is:

In the trigger:
  send a message in a pipe (cf: dbms_pipe) indicating the batch job you want to run...

In Unix:
have a process (C, shell running SQLPlus, or equivalent) which

  1. listens on the pipe in an endless loop
  2. on reception of a pipe message, run the command

Note that pipes are not transaction dependent so if you roll back, the pipe will be still be "sent"

Cheers
--



Connor McDonald
BHP Information Technology
Perth, Western Australia
"The difference between me and a madman is that I am not mad" Received on Wed Sep 30 1998 - 04:26:26 CDT

Original text of this message

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