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)
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
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “Script to List the subscribers to a Advanced queue in Oracle 10g”
Post a Comment