RE: How to invoke stored procedures from another instance?
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 [EMAIL PROTECTED] -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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to invoke stored procedures from another instance?
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 -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: How to invoke stored procedures from another instance?
Re: How to invoke stored procedures from another instance?
For this AQ or possibly streams in 9i would be the best, but there is a bit of work to get either set up and functioning the way you want. The big benefit is done right you cant lose a message from one system to the other. This is fundamentally EAI integration without the messaging server. You could effectively though have an EAI adaptor for your application with a bit of additional thought Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = If people did not sometimes do silly things, nothing intelligent would ever get done. - Ludwig Wittgenstein = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Ranganath K [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 28/08/2003 08:15 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How to invoke stored procedures from another instance? Hi listers, 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 1. Trigger on the table invoking the procedures of the other instance 2. Using dbms_alert 3. Some kind of polling mechanism Triggers we would like to avoid. Options we are left with are dbms_alert and polling mechanism. Is it possible to use dbms_alert in this case? If yes how? 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. Any help in this regard is very much appreciated. Thanks and Regards, Ranganath MailFiler [RK-FM8B9B3] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to invoke stored procedures from another instance?
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 [EMAIL PROTECTED] -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 -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
