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