CODEWITHSUNDEEP
oracle-databasequeueoracle-aq
Patryk Dobrowolski
Patryk Dobrowolski

Reputation: 1595

How to join multiple consumers oracle AQ queue to regular queue? What am I doing wrong?

I tried to develop queueing mechanism for our application. My idea is to prepare standard solution where there is one application (app0) producing events and one or more applications (app1, app2) which consume them. I need publisher/subscriber queue (topic), one queue for each consumer application and make regular queues subscribers of topic:

[app0] --enqueues-> [topic] <--subscribes-- [queue1] <--dequeues-- [app1]
                            <--subscribes-- [queue2] <--dequeues-- [app2]

I tried this:

DECLARE
    subscriber sys.aq$_agent;
    v_queue_name_topic VARCHAR2(200) := 'my_topic';
    v_queue_name_consumer_1 VARCHAR2(200) := 'my_consumer_1';
    v_queue_name_consumer_2 VARCHAR2(200) := 'my_consumer_2';
BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 't_' || v_queue_name_topic, queue_payload_type => 'SYS.AQ$_JMS_OBJECT_MESSAGE', multiple_consumers => true);
    DBMS_AQADM.CREATE_QUEUE (queue_name => v_queue_name_topic, queue_table => 't_' || v_queue_name_topic);
    DBMS_AQADM.START_QUEUE (queue_name => v_queue_name_topic);
    
    DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 't_' || v_queue_name_consumer_1, queue_payload_type => 'SYS.AQ$_JMS_OBJECT_MESSAGE', multiple_consumers => false);
    DBMS_AQADM.CREATE_QUEUE (queue_name => v_queue_name_consumer_1, queue_table => 't_' || v_queue_name_consumer_1);
    DBMS_AQADM.START_QUEUE (queue_name => v_queue_name_consumer_1);
    
    DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 't_' || v_queue_name_consumer_2, queue_payload_type => 'SYS.AQ$_JMS_OBJECT_MESSAGE', multiple_consumers => false);
    DBMS_AQADM.CREATE_QUEUE (queue_name => v_queue_name_consumer_2, queue_table => 't_' || v_queue_name_consumer_2);
    DBMS_AQADM.START_QUEUE (queue_name => v_queue_name_consumer_2);
    
    subscriber := sys.aq$_agent('Consumer1', v_queue_name_consumer_1, null);
    DBMS_AQADM.ADD_SUBSCRIBER(queue_name => v_queue_name_topic, subscriber => subscriber, queue_to_queue => true);
    
    subscriber := sys.aq$_agent('Consumer2', v_queue_name_consumer_2, null);
    DBMS_AQADM.ADD_SUBSCRIBER(queue_name => v_queue_name_topic, subscriber => subscriber);
    
    DBMS_AQADM.SCHEDULE_PROPAGATION(queue_name => v_queue_name_topic); 
END;

I wrote this code based on the only one example I found: https://docs.oracle.com/cd/A87860_01/doc/appdev.817/a76938/adq_aex4.htm

It seems good, but unfortunately doesn't work. I tried to mess up a little bit with adding subscribers and a little bit with scheduling propagation. Nothing helped.

I expect this solution to pass messages from topic to queue1 and queue2, but messages are not passed. They stay in original queue.

What am I doing wrong?

Upvotes: 2

Views: 1320

Answers (1)

Ranagal
Ranagal

Reputation: 317

May be because, you are using very old doc for Queue creation. Oracle now has reached 20c. The one in market now is 19c. Kindly use the below doc for your reference.

Click here

Upvotes: -2

Related Questions