Rem Filename   : watcher_queuestatus.sql
Rem 
Rem Parameters : &1 = [owner(pattern).]queue(pattern) 
Rem              The queues that have to be listed.
Rem
Rem Modification history
Rem --------------------
Rem 23-May-2005  Intial creation.
Rem
Rem =======================================================================================================
Rem
Rem Description : Lists the number of messages on all matching queues.status of all matching queues.
Rem               The results are written to standard output in 4 columns :
Rem
Rem                 Column 1 (pos  1-25) : The name of the queue.
Rem                 Column 2 (pos 26-33) : Number of messages in the queue in the state ’WAITING’.
Rem                 Column 3 (pos 34-41) : Number of messages in the queue in the state ’READY’.
Rem                 Column 4 (pos 42-49) : Number of messages in the queue in the state ’EXPIRED’.
Rem
Rem Sample output
Rem -------------
Rem MXXXXXXX<PDB>  @watcher_queuestatus strm_admin.%
Rem AQ$_QU_PDB_TO_RDB_FAST_E        0       0         0
Rem AQ$_QU_PDB_TO_RDB_SLOW_E        0       0         0
Rem QU_PDB_TO_RDB_FAST              0       0         0
Rem QU_PDB_TO_RDB_SLOW              0       0         0
Rem
Rem =======================================================================================================

SET PAGESIZE  10000
SET TRIMSPOOL ON
SET VERIFY    OFF
SET FEEDBACK  OFF
SET TERMOUT   OFF
SET NEWPAGE   NONE
SET HEADING   ON

-- Disect the input argument, and get the owner name and table name into two seperate 
-- substitution variables. The owner name defaults to the current user.

DEFINE InputOwnerFilter  = ' '
DEFINE InputQueueFilter  = ' '
COLUMN queue_owner NOPRINT NEW_VALUE InputOwnerFilter
COLUMN queue_name  NOPRINT NEW_VALUE InputQueueFilter

SELECT decode(instr('&&1','.'), 0, upper('&1'),  /*Default to current user.*/
                                upper(substr('&&1',1,instr('&&1','.')-1))) queue_owner,
       decode(instr('&&1','.'), 0, upper('&2'),  /*Only the queue name was passed in.*/
                                upper(substr('&&1',instr('&&1','.')+1))) queue_name
  FROM dual;

CLEAR COLUMNS
SET TERMOUT      ON

COLUMN QueueName    FORMAT A35      HEADING QueueName
COLUMN NbrWait      FORMAT 999999   HEADING NbrWait
COLUMN NbrReady     FORMAT 999999   HEADING NbrReady
COLUMN NbrExpired   FORMAT 999999   HEADING NbrExpired

SELECT dba_queues.name   "QueueName",
       v$aq.waiting      "NbrWait",
       v$aq.ready        "NbrReady",
       v$aq.expired      "NbrExpired"
  FROM dba_queues, v$aq 
 WHERE dba_queues.qid = v$aq.qid
   AND dba_queues.owner like '%&InputOwnerFilter%'
   AND dba_queues.name  like '%&InputQueueFilter%'
 ORDER BY name;


UNDEFINE InputOwnerFilter
UNDEFINE InputQueueFilter