Rem drv: <migrate type="data_upgrade" version="10.2.0.1"/>
Rem
Rem $Header: emx_data_upgrade.sql 03-aug-2005.00:08:19 caroy Exp $
Rem
Rem emx_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      emx_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    caroy       08/03/05 - caroy_bug-4496322
Rem    caroy       07/18/05 - Created
Rem

INSERT INTO mgmt_management_plugins(target_type, mp_version, mp_guid,
                                    import_date, hwm_status, 
                                    functional_description,
                                    requirements_description)
  SELECT target_type, te_version, te_guid, import_date, hwm_status,
         functional_description, requirements_description
         FROM mgmt_target_extensions;

INSERT INTO mgmt_mp_props(mp_guid, prop_name, prop_value)
  SELECT te_guid, prop_name, prop_value
         FROM mgmt_te_props;

INSERT INTO mgmt_mp_mechanisms(mp_guid, mechanism_id, mechanism_type, 
                               prop_name, prop_value)
  SELECT te_guid, mechanism_id, mechanism_type, prop_name, prop_value
         FROM mgmt_te_mechanisms;

INSERT INTO mgmt_mp_files(mp_guid, file_id, file_name, file_type,
                          file_contents)
  SELECT te_guid, file_id, file_name, file_type, file_contents
         FROM mgmt_te_files;

INSERT INTO mgmt_mp_file_props(mp_guid, file_id, prop_name, prop_value)
  SELECT te_guid, file_id, prop_name, prop_value
         FROM mgmt_te_file_props;

INSERT INTO mgmt_mp_deployments(mp_guid, agent, deploy_date, status)
  SELECT te_guid, agent, deploy_date, status
         FROM mgmt_te_deployments;

INSERT INTO mgmt_mp_deployment_errors(mp_guid, agent, severity, msg_bundle,
                                      msg_id, msg_guid)
  SELECT te_guid, agent, severity, msg_bundle, msg_id, msg_guid
         FROM mgmt_te_deployment_errors;

INSERT INTO mgmt_mp_nls_substitutions(msg_guid, sub_index, substitution)
  SELECT msg_guid, sub_index, substitution
         FROM mgmt_te_nls_substitutions;

INSERT INTO mgmt_mp_contributors(mp_guid, contributor_file_id, 
                                 contributor_name, contributor_role)
  SELECT te_guid, contributor_file_id, contributor_name, contributor_role
         FROM mgmt_te_contributors;

INSERT INTO mgmt_mp_contributor_file(mp_guid, contributor_file_id,
                                     file_contents)
  SELECT te_guid, contributor_file_id, file_contents
         FROM mgmt_te_contributor_file;

Rem Convert the sequence over from the old one to the new one so that the new
Rem one picks up where the old left off
DECLARE
  l_old_seq   NUMBER;
BEGIN
  select mgmt_te_seq.nextval into l_old_seq from dual;
  execute immediate 'drop sequence mgmt_mp_seq';
  execute immediate 'create sequence mgmt_mp_seq start with ' || l_old_seq;
END;
/

DROP INDEX MGMT_TE_FILE_PROPS_IDX_01
DROP INDEX MGMT_TE_VALIDATION_ERR_IDX_01
DROP INDEX MGMT_TE_DEPLOYMENT_ERR_IDX_01
DROP INDEX MGMT_TE_CONTRIBUTORS_IDX_01

DROP SEQUENCE mgmt_te_seq;
DROP TABLE MGMT_TARGET_EXTENSIONS;
DROP TABLE MGMT_TE_PROPS;
DROP TABLE MGMT_TE_MECHANISMS;
DROP TABLE MGMT_TE_FILES;
DROP TABLE MGMT_TE_FILE_PROPS;
DROP TABLE MGMT_TE_DEPLOYMENTS;
DROP TABLE MGMT_TE_DEPLOYMENT_ERRORS;
DROP TABLE MGMT_TE_NLS_SUBSTITUTIONS;
DROP TABLE MGMT_TE_VALIDATIONS;
DROP TABLE MGMT_TE_VALIDATION_ERRORS;
DROP TABLE MGMT_TE_CONTRIBUTORS;
DROP TABLE MGMT_TE_CONTRIBUTOR_FILE;