Re: DBMS.PIPE

A copy of this was sent to "Kristiaan Boschman" <kristiaan.boschman_at_online.be> (if that email address didn't require changing) On Mon, 8 Nov 1999 21:06:13 +0100, you wrote:

>I want to send a message to another application at the moment a transaction
>commits.
>Can I use DBMS_PIPE for this ?
>
>I would do the following
>
>In transaction Tx updates are done on table A.
>I create a trigger on table A, which sends a message to the pipe.
>Transaction Ty periodically reads the messages from the pipe.
>
>What happens if the update of table A fails, will Ty have received a
>message ? I hope not.
>
>If Ty would also receive uncommitted changes, then I need another solution.
>
>Kind regards, Lydia
>

pipe messages are sent right away (not transactional).

dbms_alert *might* be a path to look at. it is transactional but it will cause some serialization (only one person at a time can signal event 'X' -- others will block on the signal call until that person commits). Also, they can 'lose' events (like a unix signal does). If, while I am processing event 'X', two more people signal event 'X' -- i'll only get one of the signals.

AQ (advanced queues) are another option. no serialization, no lost signals.

If the messages are allowed to get out "soon" after you commit, what works nicely is to use dbms_job in the trigger. dbms_job will queue a job to be run after the transaction commits. this job does the write on the pipe. The lag time is set by you when you set the job_queue_interval init.ora parameter.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Nov 08 1999 - 15:38:31 CST