Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_remove_dbms_jobs.sql /st_emcore_10.2.0.4.1db11/1 2008/08/01 11:06:40 edemembe Exp $
Rem
Rem admin_remove_dbms_jobs.sql
Rem
Rem Copyright (c) 2003, 2008, Oracle. All rights reserved.
Rem
Rem    NAME
Rem      admin_remove_dbms_jobs.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    edemembe    07/30/08 - Bug 7286326
Rem    jsadras     03/02/05 - Bug:4162225, raise errors 
Rem    snakai      11/22/04 - update beacon avail job 
Rem    rpinnama    04/16/04 - 
Rem    rpinnama    07/03/03 - Conditionally remove DBMS jobs
Rem    rpinnama    03/31/03 - rpinnama_setup_repos_migration_main
Rem    rpinnama    03/17/03 - Created
Rem


BEGIN
  DECLARE
    l_core_ver MGMT_VERSIONS.version%TYPE;
    l_error    VARCHAR2(100) ;
    l_error_count NUMBER := 0 ;
    l_database_role VARCHAR2(32);
  BEGIN
    -- Check to see if this database is in logical standby mode.  If it is
    -- we need to exit this script because logical standby doesn't support 
    -- DBMS_JOBS.
    BEGIN   
      SELECT database_role into l_database_role
         from v$database where database_role like '%LOGICAL%';
      RETURN;  -- Exit this script if this is a logical standby db.
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;  -- Just continue normally if this is not a logical standby db.
    END;
    
    BEGIN
      SELECT version INTO l_core_ver
        FROM MGMT_VERSIONS
       WHERE component_name = 'CORE';
    EXCEPTION
      WHEN OTHERS THEN
      l_core_ver := '4.0.0.0.0';
    END;
 
    IF (l_core_ver = '4.0.0.0.0') THEN

      -- For 4.0.0 repository, the maintenance package
      -- does not have the remove_em_dbms_jobs function, so
      -- have to remove the jobs created in 4.0.0
      SAVEPOINT admin_remove_jobs_start ;
      FOR crec IN (select job, what FROM user_jobs
                    where what in (
                                 'MGMT_GENSVC_AVAIL.EXEC_AVAIL_JOB();',
                                 'EMD_COLLECTION.RUN_COLLECTIONS();',
                                 'EMD_EVALUATION.EVALSQLMETRICS();',
                                 'EMD_LOADER.ROLLUP();',
                                 'EMD_NOTIFICATION.CHECK_FOR_SEVERITIES();',
                                 'EMD_NOTIFICATION.DELIVER_NOTIFICATIONS();',
                                 'EMD_RT_ROLLUP.ROLLUP();',
                                 'EM_PING.MARK_NODE_STATUS();',
                                 'MGMT_JOB_ENGINE.apply_purge_policies();'
                                 )
                        or what like 'emd_maintenance.analyze_emd_schema%'
                                 )
      LOOP
        BEGIN
          DBMS_JOB.REMOVE(crec.job);
        EXCEPTION
          WHEN OTHERS THEN
            l_error_count := l_error_count + 1 ;
            IF l_error IS NULL
            THEN
              l_error := SUBSTR(SQLERRM,1,100) ;
            END IF ;
        END;
     END LOOP;
     IF l_error_count > 0
     THEN
       ROLLBACK TO SAVEPOINT admin_remove_jobs_start ;
       raise_application_error(MGMT_GLOBAL.UNEXPECTED_ERR,
         l_error_count||' error(s) when dropping dbms_jobs '||
           ',first error='||l_error) ; 
     END IF ;
    ELSE

      -- This is done as dynamic SQL, so that this can be run against 
      -- EM 4.0.0 repositories also
      EXECUTE IMMEDIATE 'begin EMD_MAINTENANCE.REMOVE_EM_DBMS_JOBS; end;';

    END IF;

  END;
END;
/