Rem drv: <migrate type="data_upgrade" version="10.2.0.3.0"/>
Rem
Rem $Header: jobs_data_upgrade.sql 18-jun-2007.05:05:59 lsatyapr Exp $
Rem
Rem jobs_data_upgrade.sql
Rem
Rem Copyright (c) 2006, 2007, 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    lsatyapr    06/18/07 - Backport lsatyapr_bug-6122107 from main
Rem    lsatyapr    10/24/06 - Backport lsatyapr_bug-5597503 from main
Rem    lsatyapr    06/14/07 - Bug6122107 Performance of schedule upgrade
Rem    lsatyapr    10/12/06 - Add delete_target exception (Bug 5597503)
Rem    kmanicka    05/23/06 - Created
Rem

-- convert interval schedule to daily schedule bug 5126621
DECLARE
    l_schedule MGMT_JOB_SCHEDULE_RECORD;
    l_targets MGMT_JOB_TARGET_LIST := NULL;
    l_count INTEGER := 0;
    l_commit_freq INTEGER;

    FUNCTION get_commit_frequency RETURN INTEGER IS
        l_freq INTEGER := 50000;
    BEGIN
        SELECT parameter_value INTO l_freq
        FROM   MGMT_PARAMETERS
        WHERE  parameter_name = 'job_schedule_upgrade_batchsize' ;

        RETURN l_freq;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN l_freq;
    END;
BEGIN
    l_commit_freq := get_commit_frequency;

    -- The below query is fairly sub-optimal because of full table scans. Hence
    -- we try to filter out as much as possible. Luckily, edit_job does not
    -- allow schedule to be edited for CAs or expired jobs.

    FOR c IN (SELECT /*+ USE_MERGE(MGMT_JOB_SCHEDULE MGMT_JOB) */
                     s.start_time, s.end_time, s.start_grace_period,
                     s.execution_hours, s.execution_minutes,
                     s.interval, s.months, s.days,
                     s.timezone_info, s.timezone_target_index,
                     s.timezone_offset, s.timezone_region, j.job_id
              FROM   MGMT_JOB_SCHEDULE s, MGMT_JOB j
              WHERE  s.schedule_id = j.schedule_id
              AND    j.expired = 0
              AND    j.nested = 0
              AND    j.is_corrective_action = 0
              AND    j.job_status != MGMT_JOB_ENGINE.JOB_STATUS_STOPPED
              AND    s.frequency_code = MGMT_JOB_ENGINE.INTERVAL_FREQUENCY_CODE
              AND    MOD(s.interval, 24*60) = 0)
    LOOP
        l_schedule := MGMT_JOB_SCHEDULE_RECORD(
                            MGMT_JOB_ENGINE.DAILY_FREQUENCY_CODE,
                            c.start_time, c.end_time, c.start_grace_period,
                            c.execution_hours, c.execution_minutes,
                            TRUNC(c.interval/(24*60)), c.months, c.days,
                            c.timezone_info, c.timezone_target_index,
                            c.timezone_offset, c.timezone_region);

        MGMT_JOB_ENGINE.edit_job(c.job_id, null, null, l_targets, l_schedule);

        l_count := l_count + 1;

        -- commit every l_commit_freq entries
        IF l_count = l_commit_freq THEN
            COMMIT;
            l_count := 0;
        END IF;
    END LOOP;

    IF l_count != l_commit_freq THEN
        COMMIT;
    END IF;
END;
/

DECLARE
dummy INTEGER;
BEGIN
    SELECT count(1) INTO dummy
    FROM MGMT_TARGET_DELETE_EXCEPTIONS
    WHERE table_name='MGMT_JOB_EXEC_SUMMARY';

    IF dummy = 0 THEN
        INSERT INTO MGMT_TARGET_DELETE_EXCEPTIONS (table_name)
        VALUES ('MGMT_JOB_EXEC_SUMMARY');
        COMMIT;
    END IF;
END;
/