Thursday, June 24, 2010
Script to List Messages in any Advanced JMS Queues in Oracle 10g
I have designed this script to list the specified number of XML messages from any AQJMS after clearing the necessary validations. I have tested the script in Oracle 10g.
SCRIPT:
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;
EX_END_OF_PROG EXCEPTION;
BEGIN
v_queue_name := '&Enter_the_Queue_name';
v_count := '&Enter_the_count_of_messages_need';
--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('The Queue Found with the name '||v_queue_name||' is not a JMS Queue.');
RAISE EX_END_OF_PROG;
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));
DBMS_OUTPUT.PUT_LINE(RPAD('Count of messages need',30)||':'||v_count||CHR(10)||CHR(10));
BEGIN
v_block := 'BEGIN
FOR i_data IN (SELECT *
FROM (SELECT ROWNUM num,qt.user_data.text_vc data,qt.enq_time FROM '||v_queue_table||' qt ORDER BY qt.enq_time DESC)
WHERE ROWNUM <= '||v_count||')
DBMS_OUTPUT.PUT_LINE( ''Message No :''||i_data.num||CHR(10)||''ENQUE TIME :''||i_data.enq_time);
DBMS_OUTPUT.PUT_LINE( ''DATA:''||CHR(10)||i_data.data||CHR(10)||CHR(10));
END LOOP ;
END;';
EXECUTE IMMEDIATE(v_block);
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('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:
Message No :1
ENQUE TIME :24-JUN-10 04.30.21.125000 PM
DATA:
LISTING SUCCESS
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
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.
0 Responses to “Script to List Messages in any Advanced JMS Queues in Oracle 10g”
Post a Comment