3 Replies Latest reply: Mar 5, 2013 11:46 AM by spur230 RSS

    Remove exception queue

    spur230
      I am new to AQ. We are using Oracle 11.2.0.3. We will be implementing Normal single subscriber queue. We will be using exisiting error logging table to hadle exception . So exception queue would be redundant for our workflow.

      1) Is is possible to create Queue without exception queue?
      2) Can we use queue by disabling its exception queue?
      3) How can we remove/expire message from exception queue as soon as it enqueued?

      Appreciate your input. Thanks.
        • 1. Re: Remove exception queue
          phcullen-Oracle
          Hello,

          1) Is is possible to create Queue without exception queue?

          When you create a queue table you always get a default exception queue and there should always be at least one exception queue in a queue table. When you create normal queue an exception queue is not created just the normal queue

          2) Can we use queue by disabling its exception queue?

          I am not clear on what you are asking with regard to this question. If you enqueue a message into a normal queue and it needs to be moved to the exception queue a queue monitor slave process will move the message from the normal queue to the exception queue. You can only enable an exception queue for dequeue. You cannot prevent a message from being placed on the exception queue if it is appropriate for the system to do this.

          3) How can we remove/expire message from exception queue as soon as it enqueued?

          You can setup AQ PL/SQL Notification to process any messages which are placed in the default exception queue or use DBMS_AQ.Listener to listen for enqueued messages into the exception queue.

          Thanks
          Peter
          • 2. Re: Remove exception queue
            spur230
            Great . Thanks for the prompt response, Peter.

            I will read more about AQ PL/SQL Notification and DBMS_AQ.Listener . In the mean time, do you have any example handle about what there are and how to implement
            • 3. Re: Remove exception queue
              spur230
              Thanks. I was able to register a procedure that dequeues on execption queue.