Rem drv: <migrate type="data_upgrade" version="10.2.0.4"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/10.2.0.4/jobs/jobs_data_upgrade.sql /st_emcore_10.2.0.4.1db11/1 2008/08/01 11:06:40 edemembe Exp $
Rem
Rem jobs_data_upgrade.sql
Rem
Rem Copyright (c) 2007, 2008, Oracle. All rights reserved.
Rem
Rem    NAME
Rem      jobs_data_upgrade.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/31/08 - Bug 7286326
Rem    shnavane    09/12/07 - Fix bug #6391386
Rem    lsatyapr    08/27/07 - Move relevant 10.2.0.4 upg from 11.0.0.0
Rem    rdabbott    07/28/07 - fix 6194784:delete orphan schedules
Rem    rdabbott    08/23/07 - delete_orphans -> delete_orphan_schedules
Rem    rdabbott    08/22/07 - prefix recover with EM_ instead of MGMT_
Rem    rdabbott    08/17/07 - bug 6327699: clean orphans post upgrade
Rem    rdabbott    08/23/07 - delete_orphans -> delete_orphan_schedules
Rem    rdabbott    08/22/07 - prefix recover with EM_ instead of MGMT_
Rem    rdabbott    08/17/07 - bug 6327699: clean orphans post upgrade
Rem    rdabbott    08/27/07 - Backport rdabbott_bug-6327699 from main
Rem    rdabbott    07/28/07 - fix 6194784:delete orphan schedules
Rem    shnavane    07/23/07 - Fix bug #6034526
Rem    lsatyapr    07/25/07 - Backport lsatyapr_bug_6122102a from main
Rem    lsatyapr    07/22/07 - Bug6122102 Schedule upg - revise query
Rem    lsatyapr    06/15/07 - Backport lsatyapr_bug-6122102 from main
Rem    lsatyapr    06/13/07 - Bug6122102 Schedule upgrade in blocks
Rem    ashugupt    06/01/07 - Backport ashugupt_bug-5220168 from main
Rem    ashugupt    05/28/07 - added EM_JOB_OUTPUT_MAX_LINES
Rem    dgiaimo     04/30/07 - Changing 10.2.4.0 to 10.2.0.4
Rem    kmanicka    04/26/07 - upgrade for bug 5895148
Rem    kmanicka    04/26/07 - Created
Rem


INSERT INTO MGMT_PARAMETERS
       (parameter_name, parameter_value, parameter_comment, internal_flag)
VALUES ('EM_JOB_OUTPUT_MAX_LINES', '2000', 'Max lines of step output in the job notifications', 0);

DECLARE
  l_database_role VARCHAR2(32);
BEGIN
    BEGIN
      -- Check to see if this database is in logical standby mode.  If it is
      -- we need to skip submitting jobs because logical standby doesn't 
      -- support DBMS_JOBS.
      SELECT database_role into l_database_role
         from v$database where database_role like '%LOGICAL%';
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- bug 6327699: clean orphans post upgrade
      -- submit a dbms job so this will happen '1 day later'
      EM_JOB_RECOVERY.submit_dbms_job(
         'EM_JOB_RECOVERY.delete_orphan_schedules;', SYSDATE+1);
      --bug 6391386: fix large param related issues
      EM_JOB_RECOVERY.submit_dbms_job(
         'EM_JOB_RECOVERY.fix_large_params_ref_count;', SYSDATE+4/24); 
      EM_JOB_RECOVERY.submit_dbms_job(
         'EM_JOB_RECOVERY.delete_orphan_large_params;', SYSDATE+2);
    END;
END;
/

-- Perform batch updates of various data
DECLARE
    TYPE ROWID_ARRAY IS TABLE OF ROWID;

    CURSOR job_sch_cur IS 
        SELECT ROWID FROM MGMT_JOB_SCHEDULE s
        WHERE  frequency_code = 3
        AND    (interval IS NULL OR interval < 1)
        AND    EXISTS (SELECT 1 FROM MGMT_JOB j WHERE j.schedule_id=s.schedule_id);

    l_rowids ROWID_ARRAY;
    l_commit_freq INTEGER;

    -- TODO: Move this function out and use EM_JOB_UPGRADE
    FUNCTION get_commit_frequency (p_str IN VARCHAR2) RETURN INTEGER IS l_freq INTEGER := 50000;
    BEGIN
      SELECT parameter_value
      INTO l_freq
      FROM mgmt_parameters
      WHERE parameter_name = p_str;
      RETURN l_freq;
    EXCEPTION
    WHEN others THEN
      RETURN l_freq;
    END;


BEGIN
  OPEN job_sch_cur;

  l_commit_freq := get_commit_frequency('job_schedule_upgrade_batchsize');

  LOOP
        FETCH job_sch_cur BULK COLLECT INTO l_rowids LIMIT l_commit_freq;

        EXIT WHEN l_rowids.COUNT = 0;

        FORALL j IN l_rowids.FIRST..l_rowids.LAST
            UPDATE MGMT_JOB_SCHEDULE
            SET    interval = 1
            WHERE  ROWID = l_rowids(j);

        COMMIT;
  END LOOP;

  CLOSE job_sch_cur;

END;
/