Rem drv: <create type="pkgbodys"/>
Rem
Rem $Header: upgrade_pkgbodys.sql 24-oct-2005.16:30:11 ychan Exp $
Rem
Rem upgrade_pkgbodys.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      upgrade_post_creation.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    ychan    10/24/05 - Add proc 
Rem    ychan    09/30/05 - Fix bug 4642958 
Rem    ysun     08/22/05 - Add Rac support
Rem    ychan    08/15/05 - Add Header 
Rem    ychan    08/10/05 - Fix logging 
Rem    ychan    08/05/05 - Add comment 
Rem    ychan    07/12/05 - Clean up 
Rem    ychan    06/14/05 - Created
Rem
CREATE OR REPLACE PACKAGE BODY mgmt_db_upgrade
AS
  PROCEDURE change_ver_inst(p_obj IN mgmt_target_property_cbk_obj)
  IS    
  BEGIN
    null;
  END;

  PROCEDURE change_ver_rac(p_obj IN mgmt_target_property_cbk_obj)
  IS
  BEGIN
    null;
  END;

  PROCEDURE change_ver_inst(p_obj IN mgmt_target_meta_ver_cbk_obj)
  IS    
  BEGIN
    handle_version_change(p_obj, 'oracle_database');
  END;

  PROCEDURE change_ver_rac(p_obj IN mgmt_target_meta_ver_cbk_obj)
  IS    
  BEGIN
    handle_version_change(p_obj, 'rac_database');
  END;

  
  FUNCTION get_9i_coll(p_target_type IN VARCHAR2) return COLL_LIST 
  IS
  BEGIN
    if (p_target_type = 'oracle_database') THEN
      return COLL9i_INST;
    ELSIF (p_target_type = 'rac_database') THEN
      return COLL9i_RAC;
    END IF;
    return NULL;
  END;
  
  FUNCTION get_10g_coll(p_target_type IN VARCHAR2) return COLL_LIST 
  IS
  BEGIN
    if (p_target_type = 'oracle_database') THEN
      return COLL10g_INST;
    ELSIF (p_target_type = 'rac_database') THEN
      return COLL10g_RAC;
    END IF;
    return NULL;
  END;
  
  PROCEDURE handle_version_change(p_obj IN mgmt_target_meta_ver_cbk_obj, p_target_type IN VARCHAR2)
  IS
    collFrom COLL_LIST := NULL;  
    collTo COLL_LIST := NULL;
    i NUMBER;
    l_target_name VARCHAR2(256);
    l_target_type VARCHAR2(64);
    l_target_guid mgmt_targets.target_guid%TYPE;
    l_old_version VARCHAR2(10);
    l_new_version VARCHAR2(10);

    l_old_property_value VARCHAR2(64);
    l_new_property_value VARCHAR2(64);
  
    l_metric_list      MGMT_MNTR_METRIC_ARRAY;
    l_policy_list      MGMT_MNTR_POLICY_ARRAY;
    l_collection_list  MGMT_MNTR_COLLECTION_ARRAY;
    l_operation_guid   RAW(16);   
    l_temp_var VARCHAR2(4000);
  BEGIN   
 
    l_target_type := p_target_type;    
  
    IF (p_obj.from_catprop_array is not NULL and p_obj.from_catprop_array.count > 0) THEN
      l_old_property_value := p_obj.from_catprop_array(1);
    END IF;

    IF (p_obj.to_catprop_array is not NULL and p_obj.to_catprop_array.count > 0) THEN
      l_new_property_value := p_obj.to_catprop_array(1);
    END IF;
    
    IF EMDW_LOG.p_is_info_set THEN
        emdw_log.info('l_old_property_value = ' ||l_old_property_value, MODULE_NAME);
        emdw_log.info('l_new_property_value = ' || l_new_property_value, MODULE_NAME);
    END IF;
 
    IF (l_old_property_value is not NULL and l_new_property_value is not NULL) THEN
      If (length(l_old_property_value) > 1) THEN
        l_old_version  := substr(ltrim(l_old_property_value), 1, 2);   
      END IF;
      If (length(l_new_property_value) > 1) THEN
        l_new_version  := substr(ltrim(l_new_property_value), 1, 2); 
      END IF;
         
    END IF;  

    IF ( l_old_version = '9i' and l_new_version = '10') THEN
      collFrom := get_9i_coll(p_target_type);    
      collTo := get_10g_coll(p_target_type);        
    ELSIF (l_old_version = '10' and l_new_version = '9i') THEN
      collFrom := get_10g_coll(p_target_type);    
      collTo := get_9i_coll(p_target_type);    
    END IF;

    -- set the trace
    IF EMDW_LOG.p_is_info_set THEN    
       FOR rec IN (select coll_name, schedule_ex
                            from mgmt_collections
                            where object_guid = p_obj.target_guid)
        LOOP
          emdw_log.info(rec.coll_name || '**' || rec.schedule_ex, MODULE_NAME);
        END LOOP;   
    END IF;
    
   
    if (collFrom is not NULL and collTo is not NULL) THEN
      BEGIN  
        l_target_guid := p_obj.target_guid;
        SELECT target_name
          INTO l_target_name
        FROM mgmt_targets
        WHERE target_guid = l_target_guid;

        FOR i IN collFrom.FIRST..collFrom.LAST LOOP
          -- for mgmt_metric_thresholds
          BEGIN    
            -- rename _9i coll name to _10i  
            UPDATE MGMT_METRIC_THRESHOLDS
              SET COLL_NAME = collTo(i) 
            WHERE  target_guid = l_target_guid and coll_name=collFrom(i);    
          EXCEPTION
            when DUP_VAL_ON_INDEX then  
              BEGIN
                -- delete any 10i threshold
                DELETE FROM MGMT_METRIC_THRESHOLDS
                  WHERE target_guid = l_target_guid and coll_name=collTo(i);  
                -- rename _9i coll name to _10i              
                UPDATE MGMT_METRIC_THRESHOLDS
                  SET COLL_NAME = collTo(i) 
                WHERE  target_guid = l_target_guid and coll_name=collFrom(i);           
              EXCEPTION 
                when others then  
                  IF EMDW_LOG.p_is_info_set THEN
                    emdw_log.info('duplicate thresholds - delete/update exception:  ' || SQLERRM, MODULE_NAME);
                  END IF;
              END;
            when others then  
              IF EMDW_LOG.p_is_info_set THEN
                 emdw_log.info('others - update thresholds exception:  ' || SQLERRM, MODULE_NAME);
              END IF;
          END;

          -- set the trace
          IF EMDW_LOG.p_is_info_set THEN          
            FOR rec IN (select coll_name, schedule_ex
                            from mgmt_collections
                            where object_guid = l_target_guid)
            LOOP
              emdw_log.info(rec.coll_name || '==' || rec.schedule_ex, MODULE_NAME);
            END LOOP;
          END IF;            
        
          -- for mgmt_metric_collections
          BEGIN    
            -- rename _9i coll name to _10i  
            UPDATE MGMT_METRIC_COLLECTIONS
              SET COLL_NAME = collTo(i) 
            WHERE  target_guid = l_target_guid and coll_name=collFrom(i);   
          EXCEPTION      
            when DUP_VAL_ON_INDEX then   
              BEGIN
                -- delete any 10i threshold
                DELETE FROM MGMT_METRIC_COLLECTIONS
                  WHERE target_guid = l_target_guid and coll_name=collTo(i);  
                -- rename _9i coll name to _10i              
                UPDATE MGMT_METRIC_COLLECTIONS
                  SET COLL_NAME = collTo(i) 
                WHERE  target_guid = l_target_guid and coll_name=collFrom(i);
              EXCEPTION               
                when others then
                  IF EMDW_LOG.p_is_info_set THEN
                    emdw_log.info('duplicate collections - delete/update exception:  ' || SQLERRM, MODULE_NAME);
                  END IF;              
              END;          
            when others then              
              IF EMDW_LOG.p_is_info_set THEN
                 emdw_log.info('others - update collections exception:  ' || SQLERRM, MODULE_NAME);
              END IF;
          END;
      
        END LOOP; 
        
        -- set the trace
        IF EMDW_LOG.p_is_info_set THEN
          FOR rec IN (select coll_name, schedule_ex
                            from mgmt_collections
                            where object_guid = l_target_guid)
          LOOP
            emdw_log.info(rec.coll_name || '::' || rec.schedule_ex, MODULE_NAME);
          END LOOP;
        END IF;
        
        mgmt_monitoring.get_target_settings(
            l_target_type, l_target_name, l_metric_list,l_policy_list,l_collection_list);

        -- set the trace
        IF EMDW_LOG.p_is_info_set THEN
          for indx in 1..l_collection_list.COUNT
            loop
              begin
                emdw_log.info(l_collection_list(indx).coll_name || '---' || l_collection_list(indx).schedule_ex, MODULE_NAME);
              end;
            end loop;
        END IF;

        l_operation_guid := mgmt_target_update.update_monitoring_settings(l_target_name,
                                    l_target_type ,
                                    l_metric_list,
                                    l_collection_list,
                                    1, NULL);      
                                   
        IF EMDW_LOG.p_is_info_set THEN
           emdw_log.info('Done: operation_guid = ' || l_operation_guid, MODULE_NAME);
        END IF;
        
      EXCEPTION
        when others then     
          IF EMDW_LOG.p_is_info_set THEN
                 emdw_log.info('others exception:  ' || SQLERRM, MODULE_NAME);
          END IF;
      END;        
    END IF;       
  END;  
END mgmt_db_upgrade;
/

show errors;