REM dbdrv:none
REM $Header: wfevqued.sql 26.5 2004/07/14 13:29:15 jwsmith ship $
REM ***********************************************************************
REM   NAME
REM       wfevquec.sql - 
REM   DESCRIPTION
REM       Creates Advanced Queues for the event system queue handler
REM   USAGE  
REM       sqlplus apps/pwd@dbinst @wfevquec.sql sysuser syspwd
REM ********************************************************************/
REM Queues require storage and consequently must be run in the base account
REM (autopatch will run all scripts in apps account)

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

 connect &&1/&&2;
set serveroutput on 



REM===================================================================
REM
REM Stop Queue
REM
REM===================================================================

declare
begin

  dbms_output.put_line('============================');
  dbms_output.put_line('Stopping Queue ');
  dbms_output.put_line('============================');

  dbms_aqadm.stop_queue(queue_name => '&&3');

  exception
    when others then
        dbms_output.put_line('Oracle Server Error = '||to_char(sqlcode));
        dbms_output.put_line('Oracle Server Message = '||sqlerrm);
        raise_application_error(-20000, 'Oracle Error Mkr7= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/

REM====================================================================
REM
REM Drop New Queue
REM
REM====================================================================

declare
  queue_exists exception;
  pragma EXCEPTION_INIT(queue_exists, -24006);
BEGIN

  dbms_output.put_line('=================================');
  dbms_output.put_line('dropping Queues');
  dbms_output.put_line('=================================');

 begin
  dbms_aqadm.drop_queue
	(
	  queue_name		=> '&&3'
	);

  exception
    when others then
        dbms_output.put_line('Oracle Server Error = '||to_char(sqlcode));
        dbms_output.put_line('Oracle Server Message = '||sqlerrm);
        raise_application_error(-20000, 'Oracle Error Mkr4= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
END;
/

REM====================================================================
REM
REM Drop Queue tables
REM
REM====================================================================

declare
  queue_table_exists exception;
  pragma EXCEPTION_INIT(queue_table_exists, -24001);
begin

  dbms_output.put_line('===================================');
  dbms_output.put_line('Dropping WF Event Queue Tables ');
  dbms_output.put_line('===================================');
 
  begin 
   dbms_aqadm.drop_queue_table
     (
	queue_table          => '&&3', force=>true
     );

  exception
    when others then
        dbms_output.put_line('Oracle Server Error = '||to_char(sqlcode));
        dbms_output.put_line('Oracle Server Message = '||sqlerrm);
        raise_application_error(-20000, 'Oracle Error Mkr1= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
  end;
end;
/

commit;
exit;