REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus &phase=dat+2 \
REM dbdrv: checkfile:~PROD:~PATH:~FILE \
REM dbdrv: &un_fnd &pw_fnd

REM $Header: wfeqen.sql 26.6 2002/11/14 12:40:53 rosthoma ship $
REM *******************************************************************
REM NAME
REM   wfeqen.sql - WorkFlow Exception Queue Enable
REM
REM DESCRIPTION
REM   Enables the Exception queue for dequing and sets the default
REM   number of retires on the message queue to 0.
REM
REM MODIFICATION LOG:
REM   01/2002 JWSMITH BUG 2001012 - Increased l_wf_schema to varchar2(320)
REM *******************************************************************

REM echo on

SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;

WHENEVER SQLERROR EXIT FAILURE;

REM Please enter the connect string to APPLSYS account
connect &1/&2

whenever SQLERROR continue;

WHENEVER SQLERROR EXIT FAILURE;

declare 

   l_wf_schema    varchar2(320);
   l_queue_name   varchar2(200);
   i              integer;
   l_protocol     varchar2(200);
   l_iobound      varchar2(200);
   l_queue_count  number;

begin
   select protocol, inbound_outbound, queue_count
      into l_protocol, l_iobound, l_queue_count
   from wf_queues
      where protocol = 'SMTP'
        and INBOUND_OUTBOUND = 'OUTBOUND'
        and DISABLE_FLAG = 'N';

   -- Set the default max retries to zero. This will only give
   -- the notification mailer one shot at sending the notification
   -- before sending it to the Exception queue.
   select text
      into l_wf_schema
   from wf_resources
      where name = 'WF_SCHEMA'
        and language = userenv('LANG');
   for i in 1..l_queue_count loop
      -- Build the queue name from the information we know.
      l_queue_name := l_wf_schema||'.WF_'||l_protocol||'_'||
                      substr(l_iobound,1,1)||'_'||to_char(i)||'_QUEUE';
      begin
         dbms_aqadm.alter_queue(queue_name => l_queue_name, max_retries => 0);
      exception
         when others then
            raise_application_error(-20000,sqlerrm);
      end;
   end loop;

   -- Enable the exception queue for dequeuing so that the mailer
   -- can use it.
   for i in 1..l_queue_count loop
      -- Build the queue name from the information we know.
      l_queue_name := l_wf_schema||'.AQ$_WF_'||l_protocol||'_'||
                      substr(l_iobound,1,1)||'_'||to_char(i)||'_TABLE_E';
      begin
         dbms_aqadm.Start_Queue(queue_name => l_queue_name,
                                enqueue => FALSE,
                                dequeue => TRUE);
      exception
         when others then
            raise_application_error(-20000,sqlerrm);
      end;
   end loop;

   commit;
end;
/

set verify on

commit;
exit;