Rem
Rem $Header: catnomgw.sql 21-nov-2006.14:22:51 chall Exp $
Rem
Rem catnomgw.sql
Rem
Rem Copyright (c) 2001, 2006, Oracle. All rights reserved.  
Rem
Rem  NAME
Rem    catnomgw.sql - catalog/uninstall script for Messaging Gateway
Rem
Rem  DESCRIPTION
Rem    Drops database dictionary and schema objects for the Oracle
Rem    Messaging Gateway (MGW) component. This drops the tables, views, 
Rem    packages, object types, synonyms, and so on.
Rem
Rem  NOTES
Rem    Run this script AS SYSDBA
Rem
Rem    !! --- Read This --- !! --- Read This --- !! --- Read This --- !! 
Rem
Rem    Messaging Gateway agent must not be running when this script is run.
Rem
Rem    All AQ queues and queue tables using MGW payload types should be
Rem    dropped before this script is run. This script uses FORCE to drop
Rem    the MGW types, and any AQ queue table using MGW types will be invalid
Rem    after running this script. dbms_aqadm.drop_queue_table will be able
Rem    to drop the invalid queue tables, they might be hard to find since
Rem    the queue tables are no longer listed by DBS_QUEUE_TABLE view.
Rem
Rem    The following query (run as SYS) shows all AQ queue tables whose 
Rem    payload is a MGW message type.
Rem
Rem      select owner, queue_table, object_type from dba_queue_tables
Rem        where object_type like '%SYS.MGW%_T';
Rem
Rem  MODIFIED   (MM/DD/YY)
Rem  chall       11/21/06 - drop mgwi_crypto package
Rem  chall       09/29/06 - drop mgw_agent_options view, table
Rem  chall       06/08/06 - drop Scheduler objects 
Rem  chall       04/26/06 - propagation job support (11g feature) 
Rem  chall       01/12/06 - explicitely drop notify and log queue tables 
Rem  chall       05/19/04 - MSMQ support 
Rem  chall       12/16/03 - remove obsolete (commented out) MSMQ code 
Rem  chall       07/29/03 - no startup trigger for 10i (bug 2999689)
Rem  chall       12/03/02 - drop database STARTUP and SHUTDOWN triggers
Rem  chall       11/12/02 - remove reference to never used mgw_notify synonym
Rem  chall       10/28/02 - drop FORCE for object types
Rem
Rem ==========================================================================

-- Q: Run above query and refuse to run if AQ payloads using MGW types?
-- For now, always do uninstall tasks. User is reponsible for reading and
-- doing clean up prior to running script. Must also shutdown MGW agent.

--
-- Indicate MGW component being removed
--

execute dbms_registry.removing('MGW');

rem ========================================================================

rem Basic cleanup tasks via the TSK_MGW_UNINSTALL task.
rem Removes AQ log queues, notification queus, Scheduler objects, etc.
rem This will fail if package is invalid, not exist, etc.

begin
  mgwi_admin.task(MGWI_ADMIN.TSK_MGW_UNINSTALL);
end;
/

rem ========================================================================
rem
rem Drop the AQ queue tables used for admin notification queues and 
rem propagation log queues. This is done as part of TSK_MGW_UNINSTALL but
rem repeated here in case the PL/SQL package is invalid.
rem
rem Multiple agent support added for 11g release but no extra work needed
rem since the same queue tables are used for default agent and named agents.
rem
rem ========================================================================

declare

  PROCEDURE drop_qtable( p_qtable IN VARCHAR2 )
  IS
  BEGIN
    dbms_aqadm.drop_queue_table(queue_table=>p_qtable, force=>TRUE);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -24002 THEN     -- no queue table exists
        null;
        --%dbms_output.put_line('drop qtable: '||p_qtable||' : '||SQLERRM);
      ELSE
        null;
        dbms_output.put_line('drop qtable: '||p_qtable||' : '||SQLERRM);
        --%RAISE;
      END IF;
  END drop_qtable;

begin
  -- queue table for admin notification queues
  drop_qtable('SYS.MGW_NOTIFY_QUEUE_QT');

  -- queue table for AQ native and AQ JMS propagation log queues
  drop_qtable('SYS.MGW_SEND_LOG_QT');
  drop_qtable('SYS.MGW_RECV_LOG_QT');
  drop_qtable('SYS.MGW_JMS_SEND_LOG_QT');
  drop_qtable('SYS.MGW_JMS_RECV_LOG_QT');
end;
/

rem ========================================================================
rem
rem Drop the Scheduler objects created by Messaging Gateway. This is done
rem as part of TSK_MGW_UNINSTALL but repeated here in case the PL/SQL 
rem package is invalid.
rem
rem a) Drop Scheduler job and job class used by named agents and the 
rem    default agent.
rem b) Drop Scheduler program used for MGW startup job procedure.
rem
rem ========================================================================

-- Drop a Scheduler object
--
-- p_which : 1=job, 2=job class, 3=program
-- p_name  : name of object to be dropped
-- p_force : TRUE for a forced drop, FALSE for a non-forced drop
--
create or replace procedure mgwmig_drop_scheduler_obj ( 
  p_which in pls_integer, 
  p_name  in varchar2,
  p_force in boolean default TRUE )
is
begin

  begin
    if p_which = 1 then           -- ORA-27475 if no such job
      dbms_scheduler.drop_job(p_name, p_force);
    elsif p_which = 2 then        -- ORA-27476 if no such job class
      dbms_scheduler.drop_job_class(p_name, p_force);
    elsif p_which = 3 then        --  ORA-27476 if no such program
      dbms_scheduler.drop_program(p_name, p_force);
    else
      dbms_output.put_line(
          'mgwmig_drop_scheduler_obj: unknown value for p_which; '||p_which);
    end if;
  exception
    when others then
      if SQLCODE = -27475 then null;
      elsif SQLCODE = -27476 then null;
      else
        dbms_output.put_line(
          'mgwmig_drop_scheduler_obj: name='||p_name||', which='||p_which);
        dbms_output.put_line(SQLERRM);
      end if;
  end;

end mgwmig_drop_scheduler_obj;
/

-- Drop Scheduler objects created by Messaging Gateway.
--
declare

  -- query for all the named (non-default) agents
  CURSOR mgw_named_agents_c IS 
      select agent_name from sys.mgw$_gateway 
        where nvl(agent_name,'DEFAULT_AGENT') != 'DEFAULT_AGENT';

begin

  -- drop Scheduler job and job class for named agents
  for na_rec in mgw_named_agents_c loop
    mgwmig_drop_scheduler_obj(1, 'SYS.MGW_JOB_'||na_rec.agent_name);
    mgwmig_drop_scheduler_obj(2, 'SYS.MGW_JOBCLS_'||na_rec.agent_name);
  end loop;

  -- drop Scheduler job and job class for default agent
  mgwmig_drop_scheduler_obj(1, 'SYS.MGW_JOB_DEFAULT_AGENT');
  mgwmig_drop_scheduler_obj(2, 'SYS.MGW_JOBCLS_DEFAULT_AGENT');

  -- drop agent startup program
  mgwmig_drop_scheduler_obj(3, 'SYS.MGW_STARTUP_PROGRAM');

end;
/

drop procedure mgwmig_drop_scheduler_obj;

rem ========================================================================
rem
rem  Drop MGW Entities
rem
rem ========================================================================

-- Triggers

drop trigger mgw_dbshutdown_trig;

-- Roles

drop role mgw_administrator_role;
drop role mgw_agent_role;

-- Views

drop view mgw_agent_options;
drop public synonym mgw_agent_options;

drop view mgw_gateway;
drop public synonym mgw_gateway;

drop view mgw_links;
drop public synonym mgw_links;

drop view mgw_mqseries_links;
drop public synonym mgw_mqseries_links;

drop view mgw_tibrv_links;
drop public synonym mgw_tibrv_links;

drop view mgw_msmq_links;
drop public synonym mgw_msmq_links;

drop view mgw_foreign_queues;
drop public synonym mgw_foreign_queues;

drop view mgw_subscribers;
drop public synonym mgw_subscribers;

drop view mgw_schedules;
drop public synonym mgw_schedules;

drop view mgw_jobs;
drop public synonym mgw_jobs;

-- Tables

drop table mgw$_config;
drop table mgw$_agent_options;
drop table mgw$_gateway;
drop table mgw$_mqseries_links;
drop table mgw$_tibrv_links;
drop table mgw$_msmq_links;
drop table mgw$_links;
drop table mgw$_foreign_queues;
drop table mgw$_subscribers;
drop table mgw$_schedules;

-- Packages

drop package dbms_mgwadm;
drop public synonym dbms_mgwadm;

drop package dbms_mgwmsg;
drop public synonym dbms_mgwmsg;

drop package mgwi_admin;
drop package mgwi_crypto;
drop package mgwi_const;
drop package mgw_aqdriver;
drop package mgw_notify;
drop package mgwi_reg;

-- Types

drop type mgwi_msglink force;
drop type mgwi_foreignqueue force;
drop type mgwi_subscriber force;
drop type mgwi_schedule force;

drop type mgw_aq_message_props_t force;
drop type mgw_aq_dequeue_options_t force;
drop type mgw_aq_enqueue_options_t force;

drop type mgw_notif_msg force;
drop type mgw_notif_element_list force;
drop type mgw_notif_element force;

drop type mgw_mqseries_properties force;
drop type mgw_tibrv_properties force;
drop type mgw_msmq_properties force;
drop type mgw_properties force;
drop type mgw_property force;

drop type mgw_basic_msg_t force;
drop type mgw_text_value_t force;
drop type mgw_raw_value_t force;
drop type mgw_name_value_array_t force;
drop type mgw_name_value_t force;

drop type mgw_tibrv_msg_t force;
drop type mgw_tibrv_ifields_t force;
drop type mgw_tibrv_ifield_t force;
drop type mgw_tibrv_field_t force;
drop type mgw_number_array_t force;

-- Agent library.

drop library dbms_mgwadm_agent_lib;
drop public database link mgw_agent;

--
-- Other non-public/non-supported MGW-related objects.
--

begin
  execute immediate 'drop table sys.mgw$_initfile';
exception when others then 
  if sqlcode = -942 then null; 
  else raise; end if;
end;
/

--% begin
--%  execute immediate 'drop table sys.mgw$_trace_config';
--%exception when others then 
--%  if sqlcode = -942 then null; 
--%  else raise; end if;
--%end;
--%/

--%begin
--%  execute immediate 'drop package sys.mgwi_trace';
--%exception when others then 
--%  if sqlcode = -4043 then null; 
--%  else raise; end if;
--%end;
--%/

--
-- Indicate MGW component been removed
--

execute dbms_registry.removed('MGW');

Rem === End file: catnomgw.sql ===============================================