RE: How to invoke stored procedures from another instance?

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> ineyman_at_perceptron.com> > > > -----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: http://www.orafaq.net> --

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
INET: jkstill_at_cybcon.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).