Oracle AQ and VB for publish and subscribe

From: Rob Morgan <robertmorgan_at_hotmail.com>
Date: 4 Apr 2003 01:57:46 -0800
Message-ID: <dd8749d.0304040157.69e1590c_at_posting.google.com>


Hi, I'm trying to work get a publish/subscribe system up and running using Oracle AQ on oracle Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production via Oracle Objects. What I want is to use a trigger causing updates to a table to send broadcasts to all clients interested in the record updated so that relevant action can be taken. I've created test a publisher and consumer but when the publisher attempts to send a message, a no subscribers error is returned. This happens with both persistent and non-persistent (which is what I actually want to use) queues. Oracle queueing seems very difficult to get up and running! Any ideas?

Thanks



Creates

'EXECUTE dbms_aqadm.create_queue_table (Queue_table=>
'MultiConsumerMsgs_qtab',Multiple_consumers=> TRUE,Queue_payload_type

   => 'Message_type');
'EXECUTE dbms_aqadm.create_queue (Queue_name=>
'MultiConsumerMsg_queue',Queue_table=> 'MultiConsumerMsgs_qtab')
'EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>
'MultiConsumerMsg_queue');
'EXECUTE dbms_aqadm.drop_queue_table ( queue_table =>
'MultiConsumerMsgs_qtab');
'EXECUTE dbms_aqadm.start_queue (queue_name =>
'MultiConsumerMsg_queue');
'EXECUTE dbms_aqadm.stop_queue (queue_name =>
'MultiConsumerMsg_queue');

or  

 'EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>
'MultiConsumerMsg_queue');

 'exec dbms_aqadm.create_np_queue(queue_name =>
'MultiConsumerMsg_queue', multiple_consumers => true);
'EXECUTE dbms_aqadm.start_queue (queue_name =>
'MultiConsumerMsg_queue');
'EXECUTE dbms_aqadm.stop_queue (queue_name =>
'MultiConsumerMsg_queue');

Consumer


   Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")    Set Db = OO4OSession.OpenDatabase("db", "user/pass", 0)

   Set Q = Db.CreateAQ("MultiConsumerMsg_queue")

   s = "test"

   Q.Consumer = s   

   Q.MonitorStart Me, Q, s, ORAAQ_ANY

Public Sub NotifyMe(ByVal Ctx As Variant, ByVal Msgid As Variant) ...
End Sub

Publisher


  Set Q = Db.CreateAQ("MultiConsumerMsg_queue")   Q.Consumer = "test"
  Q.Visible = ORAAQ_ENQ_IMMEDIATE

  Set msg = Q.AQMsg
  msg.Value = "Enqueue the first message to a RAW queue."

 'Q.Consumer = "test"
'Enqueue the message

  Q.Enqueue Received on Fri Apr 04 2003 - 11:57:46 CEST

Original text of this message