Re: Oracle AQ - Exception Q Housekeeping

From: Yong Huang <yong321_at_yahoo.com>
Date: 29 Nov 2004 09:39:17 -0800
Message-ID: <b3cb12d6.0411290939.1751f954@posting.google.com>

jon_at_liebling.co.uk (Jonathan) wrote in message news:<61dc311.0411260559.255b7e4_at_posting.google.com>...
> I have been trying to fnid out how to "DEQUEUE" or indeed delete
> messages older than a given date (EG: 1 month) from the exception Q.
>
> I get several errors trying different methods.
> I can dequeue find from the actual queue, and have defined our own
> (rather than the default) exception queue in its own table.
>
> Messages appear "EXPIRED" in the exception queue for all the right
> reasons.
> However I don't seem to be able to delete(dequeue?) the messages from
> here?
>
> How do I tackle this?
>
> 1: An exception queue does not support subscribers but I get the
> error:
>
> ERROR:-25231 - Error De-queueing from the Queue....! ORA-25231:
> cannot dequeue
> because CONSUMER_NAME not specified
>
> when attempting to execute?
>
> Am I missing something here, dont tell me you have to drop the queue
> to clear it down?
>
> This is my create:
> /* create Exception Queue table for connactionq information */
> DBMS_AQADM.CREATE_QUEUE_TABLE(
> Queue_Table => 'excep_q_t'
> , queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE'
> , sort_list => 'PRIORITY,ENQ_TIME'
> , COMMENT => ''
> , multiple_consumers => TRUE
> , storage_clause => ''
> , compatible => '8.1'
> );
>
> /* Create a Exception queue for the queue table */
> DBMS_AQADM.Create_Queue(
> queue_name => 'excep_q'
> , Queue_Table => 'excep_q_t'
> , queue_type => DBMS_AQADM.EXCEPTION_QUEUE
> , COMMENT => 'Whatever'
> );
>
> /* The DEQUEUE part of my DEQUEUE proc: */
>
> dequeue_options.wait := DBMS_AQ.NO_WAIT;
> dequeue_options.dequeue_mode := DBMS_AQ.REMOVE_NODATA; /* Just
> want to delete message */
>
> DBMS_AQ.DEQUEUE(queue_name => activation_qname,
> dequeue_options => dequeue_options,
> message_properties => message_properties,
> payload => MESSAGE,
> msgid => EXP_REC.MSG_ID);
> COMMIT;
>
> Regards
> Jon

I can't even go as far as you. An exception queue can't be enabled. Here's the code I ran:

begin
 DBMS_AQADM.CREATE_QUEUE_TABLE

  (   Queue_Table         => 'excep_q_t'
    , queue_payload_type  => 'SYS.AQ$_JMS_TEXT_MESSAGE'
    , sort_list           => 'PRIORITY,ENQ_TIME'
    , COMMENT             => ''
    , multiple_consumers  => TRUE
    , storage_clause      => ''
    , compatible          => '8.1'

  );
end;
/

begin
  DBMS_AQADM.Create_Queue(

      queue_name      => 'excep_q'
    , Queue_Table     => 'excep_q_t'
    , queue_type      => DBMS_AQADM.EXCEPTION_QUEUE
    , COMMENT         => 'Whatever'

  );
end;
/

Now the error:

SQL> exec dbms_aqadm.start_queue(queue_name => 'excep_q') BEGIN dbms_aqadm.start_queue(queue_name => 'excep_q'); END;

*
ERROR at line 1:
ORA-24017: cannot enable enqueue on QUEUE, YONG.EXCEP_Q is an exception queue

ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3551
ORA-06512: at "SYS.DBMS_AQADM", line 217
ORA-06512: at line 1

If I don't start_queu and execute the following, I get ORA-25226:

create type message_type as object (subject varchar2(30), text varchar2(80));
/

declare
  do dbms_aq.dequeue_options_t;
  mp dbms_aq.message_properties_t;
  msgid raw(16);
  msg message_type;
begin
  do.wait := DBMS_AQ.NO_WAIT;
  do.dequeue_mode := DBMS_AQ.REMOVE_NODATA; /* Just want to delete message */
  DBMS_AQ.DEQUEUE(queue_name => 'excep_q',

         dequeue_options    => do,
         message_properties => mp,
         payload            => msg,
         msgid              => msgid);

end;
/

Can you post complete code so other people without your environment can reproduce?

Yong Huang Received on Mon Nov 29 2004 - 11:39:17 CST