Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to invoke stored procedures from another instance?

RE: How to invoke stored procedures from another instance?

From: Jared Still <>
Date: Thu, 28 Aug 2003 17:14:30 -0800
Message-ID: <>

That sounds a lot like materialized views.

On Thu, 2003-08-28 at 08:14, Igor Neyman wrote:
> It could be a combination of trigger/pooling.
> Trigger writes changes locally into some kind "queue" table.
> The second instance is pooling this "queue" table (using db link) at
> it's own rate without affecting transactions against original table.
> Also, in this case when network is down, original instance is not
> affected, and when network restored the second instance picks up where
> it stopped before network was down.
> I have this mechanism implemented here, and it works pretty smoothly.
> Igor Neyman, OCP DBA
> -----Original Message-----
> Stephane Faroult
> Sent: Thursday, August 28, 2003 6:00 AM
> To: Multiple recipients of list ORACLE-L
> >
> >Hi listers,
> >=20
> >Assume that there are two instances in Oracle. Both
> >instances are on =
> >different machines and different Oracle versions.
> >There is a table on =
> >first instance. Any update on this table should
> >invoke stored procedures =
> >on the second instance. This should be real time
> >based. Options we =
> >looked at are
> >=20
> >1. Trigger on the table invoking the procedures of
> >the other instance
> >2. Using dbms_alert
> >3. Some kind of polling mechanism
> >=20
> >Triggers we would like to avoid. Options we are
> >left with are dbms_alert =
> >and polling mechanism.=20
> >=20
> >Is it possible to use dbms_alert in this case? If
> >yes how?
> >=20
> >Can you think of some kind of polling mechanism
> >which will satisfy the =
> >need of real time communication? Updates on the
> >table is done at a very =
> >fast rate, hence processing should also be at a
> >fast rate.
> >=20
> >Any help in this regard is very much appreciated.
> >=20
> >Thanks and Regards,
> >=20
> >Ranganath
> >=20
> I agree with your reluctance to use triggers; the problem is that
> whenever the second instance is down, then you couldn't do anything on
> the first. Basically, what you want to implement are near real-time
> although not quite synchronous snapshots.
> I have never used DBMS_ALERT in this way, but I think that it would be
> possible to have a database link on the second instance referencing the
> first one and invoking DBMS_ALERT through it. Beware with DBMS_ALERT
> though, my memories are not very fresh but there are some problems with
> COMMITs (which you can workaround with autonomous transactions, but then
> the alertee can be woken up by a rolled back transaction, a case which
> has to be handled by your code); DBMS_PIPE is another solution, which
> also has its flaws.
> Avanced queuing seems to me to be a fine mess, but perhaps it's worth a
> look too.
> Regards,
> Stephane Faroult
> Oriole
> --
> Please see the official ORACLE-L FAQ:
> --

Please see the official ORACLE-L FAQ:
Author: Jared Still

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Aug 28 2003 - 20:14:30 CDT

Original text of this message