Rem drv: <migrate type="data_upgrade" version="10.2.0.1"/>
Rem
Rem $Header: jobs_data_upgrade.sql 30-aug-2005.13:38:54 skini Exp $
Rem
Rem jobs_data_upgrade.sql
Rem
Rem Copyright (c) 2005, 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       skini    08/30/05 - Broken creds data upgrade 
Rem       skini    08/29/05 - Broken creds data upgrade 
Rem       chyu     07/18/05 - adding the upgrade header 
Rem       scgrover 07/14/05 - add extedned sql trace 
Rem       skini    07/12/05 - skini_gcbugs
Rem

-- Any data upgrade code for 102020 goes here, before calling
-- jobs_init.
BEGIN
  INSERT INTO MGMT_PARAMETERS
    (parameter_name, parameter_value, parameter_comment)
  VALUES
    ('EST_JOB', 'OFF', 'Extended SQL trace for MGMT_JOB_ENGINE.apply_purge_policies() dbms jobs. ' ||
                           'Use MGMT_JOB_ENGINE.DBMSJOB_EXTENDED_SQL_TRACE_ON(boolean) to alter');

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    UPDATE MGMT_PARAMETERS SET parameter_value = 'OFF' WHERE parameter_name = 'EST_JOB';
    COMMIT;
END;
/

-- Make sure that older jobs that have the singleTarget attribute on end
-- up being suspended if they have no credentials
DECLARE
l_ret NUMBER;
BEGIN
    FOR crec IN (SELECT job_id, execution_id, e.job_type_id, 
                        j.job_type_category
                 FROM   MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB_TYPE_INFO j
                 WHERE  e.job_type_id=j.job_type_id
                 AND    status=MGMT_JOB_ENGINE.SCHEDULED_STATUS
                 AND    j.suspend_on_nocreds=1) LOOP

        IF crec.job_type_category != MGMT_JOB_ENGINE.JOBTYPE_CATEGORY_HIDDEN THEN
            l_ret := MGMT_JOB_ENGINE.compute_cred_info(crec.job_id, 
                                                       crec.execution_id,
                                                       null, 1, 0);
            l_ret := MGMT_JOB_ENGINE.compute_cred_info(crec.job_id, 
                                                       crec.execution_id,
                                                       null, 0, 0);
        END IF;
    END LOOP;

    -- Suspend any executions that are marked as having no credentials
    MGMT_JOB_ENGINE.suspend_cred_execs;
END;
/
COMMIT;

@&EM_SQL_ROOT/core/v102020/jobs/jobs_init.sql