rem
rem This script can be used to purge notification messages from the
rem notification queue when they have Expired.
rem
rem USAGE
rem Logon as the OWB Repository Owner (OWBSYS)
rem    @<OWB-HOME>/owb/rtp/sql/purge_notifications.sql
rem
rem DESCRIPTION
rem The notification queue holds messages that can be used to notify clients 
rem about the progress of deployment/execution jobs whilst the job is being
rem performed.
rem The fix for bug 5899763 ensures messages are queued with an expiry date.
rem Such a date changes the state of the message and does not remove them
rem from the message table (wb_rt_notify_queue_tab).
rem
rem You can use this script to purge these expired messages.
rem
rem (Note that this script will only purge messages that were enqueued
rem  after fix for bug 5899763 was applied. If you have many older messages
rem  then you could use the following delete statement:-
rem    delete from WB_RT_NOTIFY_QUEUE_TAB where 
rem       enq_time<to_date('15-aug-2007','dd-mon-yyyy');
rem

set verify off;
declare
  l_po_t dbms_aqadm.aq$_purge_options_t;
begin
  l_po_t.block := TRUE;
  dbms_aqadm.purge_queue_table(
       queue_table => 'WB_RT_NOTIFY_QUEUE_TAB',
       purge_condition => 'QTVIEW.MSG_STATE=''EXPIRED''',
       purge_options => l_po_t);
end;
/