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;
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.
Subscribe to:
Post Comments (Atom)
1 comment:
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?
Post a Comment