Simulate a scenario where message goes into exception queue

How to simulate an exception during enqueue/dequeue/browse of oracle aq so that message goes into exception queue. I am newbie and yet to go through subscribers and consumer. What I hv done is ----

BEGIN dbms_aqadm.drop_queue_table ( 
   queue_table        => 'demo_queue_table', 
   force              => TRUE); 
END;
---------------------  Creation of queue table 
BEGIN
 DBMS_AQADM.CREATE_QUEUE_TABLE (
    queue_table        => 'demo_queue_table',
    queue_payload_type => 'demo_queue_payload_type'
    );
END;
-------------------- Creation and starting of the queue
BEGIN
     DBMS_AQADM.CREATE_QUEUE (
        queue_name  => 'demo_queue',
        queue_table => 'demo_queue_table'
        );

     DBMS_AQADM.START_QUEUE (
        queue_name => 'demo_queue'
        );
END;
---------------------Exception Queue 
BEGIN  
  dbms_aqadm.create_queue(
  queue_name => 'demo_queue_e',
  queue_table => 'demo_queue_table',
  queue_type => DBMS_AQADM.EXCEPTION_QUEUE,
  dependency_tracking => FALSE,
  comment => 'EXCEPTION QUEUE OF DEMO QUEUE TABLE');

  -- start queue
  dbms_aqadm.start_queue('DEMO_QUEUE_E',enqueue => FALSE, dequeue => TRUE);
END;


BEGIN 
 dbms_aqadm.start_queue('AQ$_DEMO_QUEUE_TABLE_E',enqueue => FALSE, dequeue => TRUE);
 END;
------------- Enqueue with Exception queue specified
DECLARE
       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;
  BEGIN
      o_payload := demo_queue_payload_type('Message'); 
      r_message_properties.exception_queue := 'DEMO_QUEUE_E';
      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('*** Exception Queue is [' || o_payload.message || '] ***'); 
       DBMS_OUTPUT.PUT_LINE('*** Exception Queue is [' || r_message_properties.exception_queue || '] ***');   
     COMMIT; 
   END;
   ------------------------------ BRowse  
   DECLARE  
      r_dequeue_options    DBMS_AQ.DEQUEUE_OPTIONS_T;
      r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
      v_message_handle     RAW(16);
      o_payload            demo_queue_payload_type;  
   BEGIN 
      r_dequeue_options.dequeue_mode := DBMS_AQ.REMOVE;  
       DBMS_AQ.DEQUEUE(
         queue_name         => 'demo_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 || '] ***');
    --  DBMS_OUTPUT.PUT_LINE('*** Browsed message is [' || r_message_properties.exception_queue || '] ***');
   END;
  -----------------------------------Dequeue
  DECLARE  
      r_dequeue_options    DBMS_AQ.DEQUEUE_OPTIONS_T;
      r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
      v_message_handle     RAW(16);
      o_payload            demo_queue_payload_type;   
      no_messages            exception;
      pragma                       exception_init (no_messages, -25263);
   BEGIN   
      DBMS_AQ.DEQUEUE(
         queue_name         => 'demo_queue',
         dequeue_options    => r_dequeue_options,
         message_properties => r_message_properties,
         payload            => o_payload,
         msgid              => v_message_handle
         );  
      DBMS_OUTPUT.PUT_LINE('*** Dequeued message is [' || o_payload.message || '] ***');   
     COMMIT;   
   END;
   ---------------------------------start the error queue---------------------------------------------
   exec dbms_aqadm.start_queue('AQ$_DEMO_QUEUE_TABLE_E',false, true);

I am familier with enqueue , dequeue, browse functionality now. I just want to see how exception queue works, and do some experiments on exception queue (browse and dequeue). Theoritically it is not possible to enqueue in exception queue. SO I have to generate some scenario where message goes into exception queue during enqueue ,dequeue or browse.

---

1) Yod don't need create exception queue. It is create implicitly durgin DBMS_AQADM.CREATE_QUEUE. select * from user_queues; .
2) Set message expiration time.

   declare
    v_enqueue_options    dbms_aq.enqueue_options_t;
    v_message_properties dbms_aq.message_properties_t;
    v_message_handle     raw(16);
    recipients DBMS_AQ.aq$_recipient_list_t;
  begin
    v_message_properties.expiration := 1; -- <- here
    dbms_aq.enqueue(queue_name         => 'schema.queue_name'
                   ,enqueue_options    => v_enqueue_options
                   ,message_properties => v_message_properties
                   ,payload            => self
                   ,msgid              => v_message_handle);
  end;

链接地址: http://www.djcxy.com/p/76426.html

上一篇: 神谕

下一篇: 模拟消息进入异常队列的场景