Oracle AQ - Exception Q Housekeeping

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 Received on Fri Nov 26 2004 - 07:59:36 CST