Loading

Wednesday, June 16, 2010

Script to List the subscribers to a Advanced queue in Oracle 10g

Below script will help you list the subscribers related to a Advanced queue in Oracle 10g


SET serveroutput ON

DECLARE
v_queue_table   ALL_QUEUES.queue_table%TYPE;
v_object_type   all_queue_tables.object_type%TYPE;
v_queue_name    ALL_QUEUES.name%TYPE;
v_block         VARCHAR2(4000);
v_count         NUMBER := 0;
EX_END_OF_PROG  EXCEPTION;

BEGIN

v_queue_name := '&Enter_the_Queue_name';

--Checking the availablity of the Queue

BEGIN
  SELECT queue_table
    INTO v_queue_table
    FROM ALL_QUEUES
   WHERE name = v_queue_name;
  
  SELECT object_type
    INTO v_object_type
    FROM all_queue_tables
   WHERE queue_table = v_queue_table;
  
  IF v_object_type <> 'SYS.AQ$_JMS_MESSAGE' THEN
   DBMS_OUTPUT.PUT_LINE('NOTE: The Queue Found with the name '||
                         v_queue_name||
                        ' is not a JMS Queue.'
                        ||CHR(10)
                       );
    
  END IF;
  
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('No Queue Found with the name '||
                        v_queue_name
                      );
  RAISE EX_END_OF_PROG;
END;


DBMS_OUTPUT.PUT_LINE('Inside Enque Message Unit'||CHR(10));
DBMS_OUTPUT.PUT_LINE('Input Parameters'||CHR(10));
DBMS_OUTPUT.PUT_LINE(RPAD('Queue Name',30)||':'||v_queue_name||CHR(10));

BEGIN
 FOR data IN (SELECT ROWNUM num,consumer_name
                FROM ALL_QUEUE_SUBSCRIBERS
               WHERE queue_name = v_queue_name)
 LOOP
 v_count := data.num;
 DBMS_OUTPUT.PUT_LINE( CHR(10)||
                       'SUBSCRIBER No:'||
                       data.num||CHR(10)||
                       'Consumer Name:'||
                       data.consumer_name
                     );
 END LOOP;

 IF v_count = 0 THEN
 DBMS_OUTPUT.PUT_LINE('No Subscribers were Found');
 END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The following error occured while fetching the data '||
                      CHR(10)||'CODE : '||
                      SQLCODE|| CHR(10)||
                      'ERROR MESSAGE :'||SQLERRM
                    );
RAISE EX_END_OF_PROG;
END;

COMMIT;


DBMS_OUTPUT.PUT_LINE(CHR(10)||'LISTING SUCCESS '||CHR(10));

EXCEPTION
WHEN EX_END_OF_PROG THEN
DBMS_OUTPUT.PUT_LINE('LISTING FAILED'||CHR(10));
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The following error occured while fetching the data '
                      ||CHR(10)||'CODE : '
                      ||SQLCODE|| CHR(10)||
                      'ERROR MESSAGE :'||SQLERRM
                    );
END;
/


SAMPLE OUTPUT:

anonymous block completed
NOTE: The Queue Found with the name DEMO_QUEUE is not a JMS Queue.

Inside Enque Message Unit

Input Parameters

Queue Name                    :DEMO_QUEUE

SUBSCRIBER No: 1
Consumer Name: DEMO_QUEUE_SUBSCRIBER

LISTING SUCCESS

0 Responses to “Script to List the subscribers to a Advanced queue in Oracle 10g”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.