Loading

Thursday, June 24, 2010

Enqueue any Advanced Queues JMS in Oracle 10g



I have designed this script to Enqueue a XML message to any AQJMS. I have tested the script in Oracle 10g.

SCRIPT:

SET serveroutput ON

DECLARE
v_queue_table     ALL_QUEUES.queue_table%TYPE;
v_enque_enabled   ALL_QUEUES.enqueue_enabled%TYPE;
v_object_type     all_queue_tables.object_type%TYPE;
v_queue_name      ALL_QUEUES.name%TYPE;
v_msg             CLOB := '';
v_enqueue_options dbms_aq.enqueue_options_t;
v_msg_props       dbms_aq.message_properties_t;
v_msg_id          RAW(16);
v_message         SYS.AQ$_JMS_MESSAGE := SYS.AQ$_JMS_MESSAGE.construct(DBMS_AQ.JMS_TEXT_MESSAGE);
EX_END_OF_PROG    EXCEPTION;

BEGIN

v_queue_name := '&Enter_the_Queue_name';
v_msg        := '&Enter_the_message';


--Checking the availablity of the Queue
BEGIN
  SELECT queue_table,enqueue_enabled
    INTO v_queue_table,v_enque_enabled
    FROM ALL_QUEUES
   WHERE name = v_queue_name;
  
  IF RTRIM(LTRIM(v_enque_enabled)) = 'NO' THEN
   DBMS_OUTPUT.PUT_LINE('The Queue Found with the name '||v_queue_name||' is not enabled for Enqueing. Please enable and retry.');
   RAISE EX_END_OF_PROG;
  END IF;
  
  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('Enq MSG',30)||':'||CHR(10)||v_msg||CHR(10)||CHR(10));

v_message.set_text(xmltype(v_msg).getstringval());

DBMS_AQ.ENQUEUE(  queue_name         => v_queue_name,
                  enqueue_options    => v_enqueue_options,
                  message_properties => v_msg_props,
                  payload            => v_message,
                  msgid              => v_msg_id);
COMMIT;

DBMS_OUTPUT.PUT_LINE('Msg Id'||v_msg_id||CHR(10));
DBMS_OUTPUT.PUT_LINE('ENQUE SUCCESS '||CHR(10));

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

/                         

SAMPLE OUTPUT:

Inside Enqueue Message Unit

Input Parameters

Queue Name                    : JMS_ABC_QUEUE

Enq MSG                       :
AhmedNaga


Msg Id 0BEE2728576041A2BF626E13D8AAA54D

ENQUE SUCCESS

0 Responses to “Enqueue any Advanced Queues JMS 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.