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||')
             LOOP
             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:
AhmedNaga


LISTING SUCCESS

0 Responses to “Script to List Messages in any Advanced JMS Queues 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.