Rem
Rem $Header: noschutl.sql 19-mar-2007.19:09:24 rramkiss Exp $
Rem
Rem noschutl.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      noschutl.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rramkiss    03/19/07 - remove e-mail notifications
Rem    rgmani      03/19/07 - Remove script for scheduler utils pkg
Rem    rgmani      03/19/07 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100


-- This file should be run as SYS. This will drop all the database objects 
-- created by the file watch feature and then drop the packages, tables and
-- types used to implement it.

-- remove all e-mail notifications
declare
CURSOR job_notifications is
   select unique n.owner, n.job_name
     from dba_sched_util_notifications n;
begin

  -- suppress all errors, this is best effort
  FOR notification IN job_notifications LOOP
  begin
    sys.scheduler_util.remove_job_email_notification(
      '"'||notification.owner||'"."'||notification.job_name||'"');
  exception when others then null;
  end;
  END LOOP;

end;
/

DECLARE
TYPE idlist IS TABLE OF VARCHAR2(30);
sql_stat   VARCHAR2(4000);
job_names idlist;
job_owners idlist;
sch_names idlist;
BEGIN
  -- Get list of owners who have file watch jobs
  sql_stat := 'SELECT owner, job_name FROM dba_scheduler_jobs ' ||
    'WHERE job_name = ''SCHEDUTIL$_FILE_WATCH_JOB''';
  EXECUTE IMMEDIATE
    sql_stat
  BULK COLLECT INTO job_owners, job_names;

  -- For each owner
  IF job_owners.EXISTS(1) THEN
    FOR i IN job_owners.FIRST..job_owners.LAST LOOP
      -- Get the schedules that the owner has created
      sql_stat := 'SELECT schedule_name FROM dba_scheduler_schedules ' ||
        'WHERE owner = :a AND event_queue_owner = :b AND ' ||
        'event_queue_name = ''SCHEDUTIL$_EVT_QUEUE''';
      EXECUTE IMMEDIATE
        sql_stat
      BULK COLLECT INTO sch_names
      USING job_owners(i), job_owners(i);

      -- For each schedule 
      IF sch_names.EXISTS(1) THEN
        FOR j IN sch_names.FIRST..sch_names.LAST LOOP
          -- Drop the schedule
          scheduler_util.drop_file_watch_schedule(
            '"' || job_owners(i) || '"."' || sch_names(j) || '"');
        END LOOP;
      END IF;
    END LOOP;
  END IF;
END;
/

-- Drop the various database objects created for this feature

DROP PACKAGE BODY scheduler_util_internal
/

DROP JAVA SOURCE "schedUtilFileWatchJava"
/

DROP PACKAGE BODY scheduler_util
/

DROP PACKAGE scheduler_util_internal
/

DROP PACKAGE scheduler_util
/

DROP TYPE BODY sched_util_filewatch_type
/

DROP TYPE sched_util_filewatch_type
/

BEGIN
  dbms_scheduler.drop_job_class(
    job_class_name => 'sched_util_file_watch_class');
END;
/

DROP TABLE schedutil$_schedule_list
/

drop view all_sched_util_parameters
/

drop view dba_sched_util_parameters
/

DROP TABLE schedutil$_parameters
/

DROP TABLE SCHEDUTIL$_TEMP_DIR_LIST
/

drop public synonym dba_sched_util_parameters;
drop public synonym all_sched_util_parameters;
drop public synonym dba_sched_util_notifications;
drop public synonym user_sched_util_notifications;
drop public synonym all_sched_util_notifications;
drop view dba_sched_util_notifications;
drop view user_sched_util_notifications;
drop view all_sched_util_notifications;
drop procedure sched_util$_job_event_handler;

drop table schedutil$_notifications;