Skip to Main Content
  • Questions
  • DBMS_AQ.LISTEN to listen to a Single/Multi-Consumer Queue

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rajesh.

Asked: January 13, 2018 - 12:16 am UTC

Last updated: March 31, 2022 - 10:21 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Dear Experts,
Need your guidance/suggestions to resolve this issue:
Part of oracle advance queueing implementation, we've to dequeue the message as soon as it has been enqueued into the queue. This should happen immediately without any manual intervention/scheduling a job. After research over some forums & metalink we found DBMS_AQ.LISTEN will help to do this job.

Got the examples from metalink - Example demonstrating using DBMS_AQ.LISTEN to listen to a Multi-Consumer Queue (Doc ID 564037.1)

Here're the steps we followed as per the above example:

1) create a QUEUE TABLE, QUEUE and start the QUEUE.
2) Subscribe the agents ( here multiple agents, doesn't matter if one or multiple for my case)
3) Created procedure which perform enqueue
4) Created procedure which perform dequeue
5) Created procedure which listens to the queue ( In side this it listens to queue and dequeue the message by calling procedure in step 4)

After the above configuration is done, followed below steps for execution:
a) In session 1, run/start the listen_m procedure to listen to the multi-consumer queue.
b) In session 2, enqueue a message using the enq_msg procedure.
c) Re-check the output of listen_m in session 1. --> which is successfully dequeueing the message.
d) In session 2, enqueue another message using the enq_msg procedure.
e) Re-check the output of listen_m in session 1. --> This is not dequeued.

Not sure why the second message was not dequeued, is my listener stopped running? or LISTENER will monitors the queue only until the first message has been enqueued after that it stops monitoring?

If LISTNER is not the right utility to achieve my requirement? If yes, what're the other alternatives which can monitor and dequeue immediately after message has been enqueued to the queue (doesn't matter it's a single or multi consumer)

You can find the script in my LIVESQL session, it's not compiled successfully because of the below error:
PLS-00201: identifier 'DBMS_AQADM' must be declared


with LiveSQL Test Case:

and Connor said...

You'll be pleased to know there's a simple fix to this one.

The usage model is that LISTEN does exactly that - "Wait until I *hear* something and stop"

So all you need to do is run this like a daemon

loop
  listen_m;
end loop;


and typically you'll have some sort of way signal it as well, eg

loop
  listen_m;
  if agent_w_msg.name = 'STOP' then 
     exit;
  end if;
end loop;


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rajesh M, January 15, 2018 - 2:55 pm UTC

Thanks Connor.

Looks like some consequences with listner running in loop.
if network glitch/DB down happened , listner comes out from the loop? and then we've to run the piece of code again?

some of the blogs given the example to use DBMS_AQ.REGISTER for dequeueing the queue using call back procedure.

Please suggest which one is better to use, LISTNER or DBMS_AQ.REGISTER ?

here's the example i found in a blog:

 BEGIN

   DBMS_AQADM.ADD_SUBSCRIBER (
      queue_name => 'demo_queue',
      subscriber => SYS.AQ$_AGENT(
                       'demo_queue_subscriber',
                       NULL,
                       NULL )
      );

    DBMS_AQ.REGISTER (
       SYS.AQ$_REG_INFO_LIST(
          SYS.AQ$_REG_INFO(
             'DEMO_QUEUE:DEMO_QUEUE_SUBSCRIBER',
             DBMS_AQ.NAMESPACE_AQ,
             'plsql://DEMO_QUEUE_CALLBACK_PROCEDURE',
             HEXTORAW('FF')
             )
          ),
       1
       );
END;
/

Connor McDonald
January 16, 2018 - 4:02 am UTC

Potentially two different use cases here.

I'd use LISTEN when I'm expecting high volume frequent activity. So I'm expecting LISTEN's to be relatively short lived (seconds).

REGISTER effectively submits a job to handle the request when one comes in. So that is a fairly large overhead to handle a message - so if you smashed it with thousands of quick fire messages, there's a considerable cost.

In the past I've typically done something like a scheduled job that runs every (say) 10 mins, and my listening loop only runs for 10mins.

That way, if it crashes, the scheduler is going to take care of a restart, plus I got logging, email notifications etc

Rajesh M, January 17, 2018 - 2:30 pm UTC

Thanks Connor for your detailed explanation, that really helps me.
Chris Saxon
January 18, 2018 - 1:35 pm UTC

Glad this helped

Rajesh M, January 19, 2018 - 5:59 pm UTC

One last question:

How can i find the listner is running or not on my queue? what're the dictionary tables stores this information?

Also how to find the objects registered for a queue (those are registered using DBMS_AQ.REGISTER) ?
Connor McDonald
January 22, 2018 - 2:09 am UTC

You could simply monitor v$session. One thing I'll often use is a lock to see if the listener is running and also control that only one listener is running, eg

SQL> declare
  2   l_listener varchar2(128);
  3   l_request  int;
  4  begin
  5   dbms_lock.allocate_unique('MY_LISTENER', l_listener);
  6   l_request := dbms_lock.request(l_listener, dbms_lock.x_mode, 0, false);
  7   --
  8   -- rest of listener stuff
  9   --
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select l.*
  2  from sys.dbms_lock_allocated a,
  3       v$lock l
  4  where a.name = 'MY_LISTENER'
  5  and a.lockid = l.id1;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ----------
00007FFC8AA4CD80 00007FFC8AA4CE00         21 UL 1073742527          0          6          0        157



So the presence of the lock means the listener is there in a session, and if I erroneously ran a second copy of the listener, it would not get the lock and hence I could terminate that second execution.

Search this site for dbms_lock.request for plenty of of examples.

dbms_aq.listen not working as expected

Alejandro, March 30, 2022 - 4:22 pm UTC

Hi,

I have 2 queues where I am using a scheduled job that runs every 10 mins, and my listening loop only running also for 10mins.

When I try to enqueue 1 msg to each queue, only the first one will pick it up, while the other will wait until the job is restarted to pick up the msgs. Any idea what I am doing wrong?

Thanks,
Alejandro

Connor McDonald
March 31, 2022 - 10:21 am UTC

We probably need to see a full test case to help us here.

But a "listen" call will respond to the first message it finds, at which point you would dequeue and the re-enter the listen phase.

But we need to see a test case.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library