Wednesday, August 31, 2005

I'm not making it up

This gem is used at times of particularly high throughput to monitor the size of queues.
PROCEDURE getqueuecount (
   queuename                  IN       VARCHAR2
  ,agentname                  IN       VARCHAR2
  ,queuecount                 OUT      INTEGER
)
IS
   queueoptions        DBMS_AQ.dequeue_options_t;
   messageproperties   DBMS_AQ.message_properties_t;
   MESSAGE             connect_db_external_q_type;
   messageid           RAW (16);
   nodequeuewaiting    EXCEPTION;
   dequeuewaiting      BOOLEAN                      := TRUE;
   nolock              EXCEPTION;
   PRAGMA EXCEPTION_INIT (nolock, -054);
   PRAGMA EXCEPTION_INIT (nodequeuewaiting, -25228);
BEGIN
   LOCK TABLE q_lock_table IN EXCLUSIVE MODE NOWAIT;
   queuecount                 := 0;
   queueoptions.WAIT          := 1;
   queueoptions.consumer_name := agentname;
   queueoptions.navigation    := DBMS_AQ.first_message;

   BEGIN
      WHILE (dequeuewaiting)
      LOOP
         DBMS_AQ.dequeue (queuename
                         ,queueoptions
                         ,messageproperties
                         ,MESSAGE
                         ,messageid
                         );
         queuecount  :=   queuecount  + 1;
      END LOOP;
   EXCEPTION
      WHEN nodequeuewaiting
      THEN
         dequeuewaiting             := FALSE;
   END;

   ROLLBACK;
EXCEPTION
   WHEN nolock
   THEN
      queuecount                 := -1;
END;

1 comment:

OraWTFGuy said...

Cor, you'd think that Oracle would supply some mechanism to identify how many messages were on a queue, eh?

What next? To work out how many rows are in a table, simply delete them all, capture SQL%ROWCOUNT, then ROLLBACK. Simple, innit?