99 lines
3.1 KiB
SQL
99 lines
3.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Search for Queues
|
|
* Parameters : 1 - owner (% - wildchar, \ - escape char) , this could also be passed as OWNER.queue_name
|
|
* 2 - Queue name (% - wildchar, \ - escape char)
|
|
* 3 - Where Clause
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 10-Aug-16 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE queue_name
|
|
UNDEFINE WHERECLAUSE
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE queue_name="&&2"
|
|
DEFINE WHERECLAUSE="&&3"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _queue_name NEW_VALUE queue_name NOPRINT
|
|
COLUMN _subqueue_name NEW_VALUE subqueue_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
COLUMN _object_status NEW_VALUE object_status NOPRINT
|
|
|
|
set term off
|
|
|
|
SELECT DECODE(UPPER('&&owner'),'','%','&&owner') "_owner"
|
|
, DECODE(UPPER('&&queue_name'),'','%','&&queue_name') "_queue_name"
|
|
FROM DUAL;
|
|
|
|
SELECT CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1)
|
|
ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner'))
|
|
END "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&queue_name'),'','%',UPPER('&&queue_name'))
|
|
END "_queue_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * O B J E C T S L I S T
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Queue Name = '&&queue_name'
|
|
PROMPT * - Where Clause = '&&WHERECLAUSE'
|
|
PROMPT *****************************************************************
|
|
|
|
|
|
|
|
COLUMN queue HEADING "Queue" FORMAT a50
|
|
COLUMN queue_type HEADING "QueueType" FORMAT a15
|
|
COLUMN retention HEADING "Retention|(sec)" FORMAT a10
|
|
COLUMN enqueue_enabled HEADING "Enqueue|Enabled" FORMAT a7
|
|
COLUMN dequeue_enabled HEADING "Dequeue|Enabled" FORMAT a7
|
|
COLUMN retry_delay HEADING "Retry|Delay" FORMAT 999999
|
|
COLUMN max_retries HEADING "Max|Retries" FORMAT 999999
|
|
COLUMN queue_table HEADING "Queue Table" FORMAT a25
|
|
COLUMN user_comment HEADING "User Comment" FORMAT a25
|
|
|
|
|
|
SELECT q.owner || '.' || q.name queue
|
|
, q.queue_type
|
|
, q.enqueue_enabled
|
|
, q.dequeue_enabled
|
|
, q.retention
|
|
, q.retry_delay
|
|
, q.max_retries
|
|
, q.queue_table
|
|
, q.user_comment
|
|
FROM dba_queues q
|
|
WHERE 1=1
|
|
AND q.owner LIKE '&&owner'
|
|
AND q.name LIKE '&&queue_name'
|
|
&&WHERECLAUSE
|
|
ORDER BY q.owner
|
|
, q.name
|
|
;
|
|
|
|
|
|
@@footer
|
|
|