Rem drv: <migrate type="data_upgrade" version="10.2.0.4" />
Rem $Header: rep_metric_data_upgrade.sql 07-sep-2007.14:20:23 gan Exp $
Rem
Rem rep_metric_data_upgrade.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      rep_metric_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    gan         09/07/07 - backport bug 6336199
Rem    gan         05/15/07 - Created, bug 4523381
Rem

DECLARE
CURSOR promoted_metrics
IS
 SELECT t.target_name, t.target_type, m.metric_name, m.metric_column
   FROM mgmt_targets t, mgmt_metrics m, mgmt_metric_dependency d
  WHERE t.target_type = m.target_type
    AND t.type_meta_ver = m.type_meta_ver
    AND (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ')
    AND (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ')
    AND (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ')
    AND (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ')
    AND (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
    AND t.target_guid = d.target_guid
    AND m.metric_guid = d.metric_guid
    AND d.rs_metric = 1;

BEGIN
  FOR rec in promoted_metrics
  LOOP
     BEGIN
        em_rep_metric.add_dummy_collection(rec.target_name,
                             rec.target_type,
                             rec.metric_name,
                             rec.metric_column,
                             ' ');

     EXCEPTION
      WHEN mgmt_global.key_already_exists THEN
           NULL; -- ignore if the collection exists.
     END;
  END LOOP;

  EM_REP_METRIC.SUSPEND_NULL_DEP_DATA_COLL;
  COMMIT;
END;
/
show errors;