VIEW: SYS.USER_QUEUE_SUBSCRIBERS

Source

View Text - Preformatted

select q.name QUEUE_NAME, t.name QUEUE_TABLE,
       s.name CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL,
       s.trans_name TRANSFORMATION, r.rule_condition RULE,
       decode(bitand(s.sub_type, 192), 64, 'PERSISTENT',
                                       128, 'BUFFERED',
                                       192, 'PERSISTENT_OR_BUFFERED',
                                       'NONE') DELIVERY_MODE,
       decode(bitand(s.sub_type, 32960), 32960, 'YES','NO')
                                       NONDURABLE,
       decode(bitand(s.sub_type, 512), 512, 'TRUE', 'FALSE') QUEUE_TO_QUEUE,
       s.subscriber_id SUBSCRIBER_ID,
       s.pos_bitmap POS_BITMAP
FROM   system.aq$_queues q, system.aq$_queue_tables t, sys.user$ cu,
        sys.user_rules r,
       TABLE(aq$_get_subscribers(cu.name, q.name, t.name,
                                 cu.name, q.eventid, t.flags)) s
where cu.user# = userenv('SCHEMAID')
and   cu.name  = t.schema
and   q.table_objno = t.objno
and   bitand(t.flags, 1) = 1 and q.usage!=1
and   s.rule_name = r.rule_name(+)

View Text - HTML Formatted

SELECT Q.NAME QUEUE_NAME
, T.NAME QUEUE_TABLE
, S.NAME CONSUMER_NAME
, S.ADDRESS ADDRESS
, S.PROTOCOL PROTOCOL
, S.TRANS_NAME TRANSFORMATION
, R.RULE_CONDITION RULE
, DECODE(BITAND(S.SUB_TYPE
, 192)
, 64
, 'PERSISTENT'
, 128
, 'BUFFERED'
, 192
, 'PERSISTENT_OR_BUFFERED'
, 'NONE') DELIVERY_MODE
, DECODE(BITAND(S.SUB_TYPE
, 32960)
, 32960
, 'YES'
, 'NO') NONDURABLE
, DECODE(BITAND(S.SUB_TYPE
, 512)
, 512
, 'TRUE'
, 'FALSE') QUEUE_TO_QUEUE
, S.SUBSCRIBER_ID SUBSCRIBER_ID
, S.POS_BITMAP POS_BITMAP FROM SYSTEM.AQ$_QUEUES Q
, SYSTEM.AQ$_QUEUE_TABLES T
, SYS.USER$ CU
, SYS.USER_RULES R
, TABLE(AQ$_GET_SUBSCRIBERS(CU.NAME
, Q.NAME
, T.NAME
, CU.NAME
, Q.EVENTID
, T.FLAGS)) S WHERE CU.USER# = USERENV('SCHEMAID') AND CU.NAME = T.SCHEMA AND Q.TABLE_OBJNO = T.OBJNO AND BITAND(T.FLAGS
, 1) = 1
AND Q.USAGE!=1 AND S.RULE_NAME = R.RULE_NAME(+)