Rem drv: <migrate type="data_upgrade" version="10.2.0.1"/>
Rem
Rem $Header: gensvc_data_upgrade.sql 19-aug-2005.14:35:33 yxie Exp $
Rem
Rem gensvc_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      gensvc_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    yxie        08/17/05 - service jobs details upgrade
Rem    chyu        07/18/05 - adding the upgrade header 
Rem    scgrover    07/15/05 - scgrover_add_dbms_job_extended_sql_trace
Rem    scgrover    07/08/05 - Created
Rem

BEGIN
  INSERT INTO MGMT_PARAMETERS
    (parameter_name, parameter_value, parameter_comment)
  VALUES
    ('EST_GENSVC', 'OFF', 'Extended SQL trace for MGMT_GENSVC_AVAIL.EXEC_AVAIL_JOB() dbms jobs. ' ||
                          'Use MGMT_GENSVC_AVAIL.DBMSJOB_EXTENDED_SQL_TRACE_ON(boolean) to alter');

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

DECLARE
  rows INTEGER := 0;
  CURSOR bcn_job_details IS 
    SELECT update_ops_details.destination_target_guid, update_ops.operation_guid,
           update_ops.job_id, update_ops.job_owner, update_ops_details.execution_id, 
           update_ops_details.execution_status, update_ops.submission_timestamp, 
           update_ops.last_updated_timestamp, update_ops_details.error_message 
      FROM MGMT_UPDATE_OPERATIONS update_ops, MGMT_UPDATE_OPERATIONS_DETAILS update_ops_details, 
           MGMT_GENSVC_UPDBCN_JOB bcn_job
     WHERE update_ops.operation_guid = update_ops_details.operation_guid
       AND update_ops.operation_guid = bcn_job.operation_guid;

BEGIN
   FOR cur_rec IN bcn_job_details 
   LOOP
     BEGIN
       -- Populate the MGMT_GENSVC_JOBS_DETAILS table
       INSERT INTO MGMT_GENSVC_JOBS_DETAILS
         (target_guid, operation_guid, job_id, 
          job_owner, execution_id, execution_status,
          submission_timestamp, last_updated_timestamp, error_message)
       VALUES
         (cur_rec.destination_target_guid, cur_rec.operation_guid, cur_rec.job_id, 
          cur_rec.job_owner, cur_rec.execution_id, cur_rec.execution_status, 
          cur_rec.submission_timestamp, cur_rec.last_updated_timestamp, cur_rec.error_message);

       rows := rows + 1;
       IF(MOD(rows, 250) = 0) THEN
         COMMIT;
       END IF;

     EXCEPTION
       WHEN DUP_VAL_ON_INDEX THEN
         -- Ignore duplicates
         NULL;
     END;

   END LOOP;

   COMMIT;
END;
/
show errors;