Thursday, June 24, 2010

Script to Resubmit a Message from Exception Queue to its Base Queue in Oracle 10g Advanced Queues




I have designed this script to resubmit a message from Exception Queue to its Base queue with necessary validations.

Prerequisites:
  1. Create a Payload type
  2. Create a Queue table
  3. Create a Queue
  4. Start the Queue
  5. Create Subscriptions
  6. Enqueue the Necessary Messages so that it enters into Exception Queue.

Basic scripts for doing the above steps are available in the link below


SCRIPT:

Below is the script for resubmission,

SET serveroutput ON

DECLARE

   r_dequeue_options    DBMS_AQ.DEQUEUE_OPTIONS_T;
   r_enqueue_options    DBMS_AQ.ENQUEUE_OPTIONS_T;
   r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
   v_message_handle     RAW(16);
   o_payload            demo_queue_payload_type; -- The custom payload type
   v_exception_msg_id   RAW(16)       := '&expection_msg_id';
   v_base_queue         VARCHAR2(100) := 'DEMO_QUEUE'; -- The Custom Queue
   v_exception_queue    VARCHAR2(100);
   v_queue_table_name   VARCHAR2(100);
   v_msg_check          VARCHAR2(1);
   v_check_query        VARCHAR2(1000);
   EX_END_OF_PROG       EXCEPTION;
  

BEGIN
   DBMS_OUTPUT.PUT_LINE(
      '*** Resubmission Starting ***'
      );
   r_dequeue_options.navigation    := DBMS_AQ.FIRST_MESSAGE;
   r_dequeue_options.dequeue_mode  := DBMS_AQ.BROWSE;   
   r_dequeue_options.visibility    := DBMS_AQ.IMMEDIATE;
   r_dequeue_options.msgid         := v_exception_msg_id;
  
   -- Find the exception Queue name --
  
   BEGIN
  
     SELECT 'AQ$_'||queue_table||'_E',queue_table
     INTO   v_exception_queue , v_queue_table_name
     FROM   ALL_QUEUES
     WHERE  name     = v_base_queue
     AND    owner      = 'SCOTT';
    
     DBMS_OUTPUT.PUT_LINE('*** Queue Found with the name '||v_base_queue||' ***');
  
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('*** No Queue Found with the name '||v_base_queue||' ***');
      RAISE EX_END_OF_PROG;
     WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(
      '*** ERROR while finding the Exception Queue name [' || SQLERRM || '] ***'
       );
     RAISE EX_END_OF_PROG;
   END;
  
   -- Checking the Presence of Message in the Queue to be Dequeued --
   BEGIN
     v_msg_check := 'N';   
     v_check_query := 'SELECT ''Y'' FROM   '
                      ||v_queue_table_name||
                      ' WHERE  q_name     = '''||v_exception_queue||'''
                      AND    msgid     = '''||v_exception_msg_id||'''';                     
    
     EXECUTE IMMEDIATE v_check_query
     INTO v_msg_check;
   
    IF v_msg_check = 'Y' THEN
    DBMS_OUTPUT.PUT_LINE(
      '*** Message Found!! Proceeding with Dequeuing ***'
       );
    ELSE
       DBMS_OUTPUT.PUT_LINE(
      '*** ERROR the message not found with the queue '||v_exception_queue|| ' ***'
       );
      RAISE EX_END_OF_PROG;
    END IF;
  EXCEPTION
  WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(
      '*** ERROR while checking the Msg existance [' || SQLERRM || '] ***'
       );
      RAISE EX_END_OF_PROG;
  END;
  
   -- Dequeue the Message from Exception Queue --
  
   DBMS_AQ.DEQUEUE(
      queue_name         => v_exception_queue,
      dequeue_options    => r_dequeue_options,
      message_properties => r_message_properties,
      payload            => o_payload,
      msgid              => v_message_handle
      );

   DBMS_OUTPUT.PUT_LINE(
      '*** Browsed message is [' || o_payload.message || '] ***'
      );
    
   -- Enqueue the Message to Base Queue -- 
  
   DBMS_AQ.ENQUEUE(
      queue_name         => 'demo_queue',
      enqueue_options    => r_enqueue_options,
      message_properties => r_message_properties,
      payload            => o_payload,
      msgid              => v_message_handle
      );
   DBMS_OUTPUT.PUT_LINE(
      '*** Resubmitted message id is [' || v_message_handle || '] ***'
      );
    DBMS_OUTPUT.PUT_LINE(
      '*** Resubmission Sucess ***'
      );
  COMMIT;

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


SAMPLE OUTPUT:

*** Resubmission Starting ***
*** Queue Found with the name DEMO_QUEUE ***
*** Message Found!! Proceeding with Dequeuing ***
*** Browsed message is [TEST7] ***
*** Resubmitted message id is [63EA09169A064893AE7F3A58D005EEF3] ***
*** Resubmission Success ***

0 Responses to “Script to Resubmit a Message from Exception Queue to its Base Queue in Oracle 10g Advanced Queues”

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.