Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/emupgrade.sql /st_emdbsa_11.2/3 2008/12/05 20:46:20 ysun Exp $
Rem
Rem emupgrade.sql
Rem
Rem Copyright (c) 2007, 2008, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      emupgrade.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    ysun        11/17/08 - adding the CELL component
Rem    chyu        05/14/08 - adding the upgrade paths from 11.1.0.7
Rem    chyu        04/11/08 - upping the version for RDBMS 11.2
Rem    chyu        01/07/08 - upping the CORE and PP component version
Rem    chyu        05/24/07 - fixing bug 6073165 - logic for running
Rem                           post_data_upgrade scripts
Rem    gsbhatia    03/15/07 - bug 4069024
Rem    gsbhatia    03/15/07 - Created
Rem

-- Contine on SQL errors
WHENEVER SQLERROR CONTINUE;

-- Mark the dbms_registry for EM component as upgrading.
DECLARE
  l_comp_name VARCHAR2(256);
BEGIN
  l_comp_name := 'EM';
  IF NOT ('&EM_REPOS_MODE' = 'CENTRAL') THEN
    EXECUTE IMMEDIATE 'BEGIN dbms_registry.upgrading(:1); END;' USING l_comp_name;
  END IF;
END;
/

-- 
-- Peform any SYS activity, like granting privileges to EM_REPOS_USER

ALTER SESSION SET CURRENT_SCHEMA=&&EM_REPOS_USER;


-- Execute user defined pre-upgrade scripts
@&EMDW_HOME/sysman/emdrep/config/pre_upgrade.sql

-- Stop All DBMS jobs..
@&EM_SQL_ROOT/core/latest/admin/admin_remove_dbms_jobs.sql

-- Run upgrade heuristics
@&EM_SQL_ROOT/db/upgrade/pre_upgrade_heuristics.sql

@&EM_SQL_ROOT/core/latest/admin/admin_grants_repos_user.sql

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE core_schema_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_core_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_core_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CORE';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_core_ver := '10.1.0.2.0';
  END;
  IF l_core_ver = '10.2.0.4.2'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_core_ver >= '10.1.0.2.0' AND l_core_ver <= '10.1.0.6.0') OR
        (l_core_ver >= '10.2.0.0.0' AND l_core_ver <= '10.2.0.4.2')
  THEN 
    :script_name := '&EM_SQL_ROOT/db/core_schema_upgrade_' || l_core_ver || '.sql';
  ELSIF substr(l_core_ver, 1, 7) = '10.1.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/core_schema_upgrade_10.1.0.6.0.sql';
  ELSIF substr(l_core_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/core_schema_upgrade_10.2.0.4.2.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&core_schema_upgrade

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE db_schema_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_db_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_db_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'DB';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    l_db_ver := '10.1.0.2.0';
  END;

  IF l_db_ver = '11.2.0.0.0'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF l_db_ver = '10.2.0.1.0' OR
        l_db_ver = '10.2.0.0.0'
  THEN
        -- need to use 10.2.0.0.0 since DBControl 10.2.0.1.0 is
        -- the 10.2.0.0.0 DB component version
    :script_name := '&EM_SQL_ROOT/db/db_schema_upgrade_10.2.0.0.0.sql';
  ELSIF (l_db_ver >= '10.1.0.2.0' AND l_db_ver <= '10.1.0.6.0') OR
        (l_db_ver >= '10.2.0.2.0' AND l_db_ver <= '10.2.0.9.0') OR
        (l_db_ver >= '11.0.0.0.0' AND l_db_ver <= '11.2.0.0.0')
  THEN
    :script_name := '&EM_SQL_ROOT/db/db_schema_upgrade_' || l_db_ver || '.sql';
  ELSIF substr(l_db_ver, 1, 7) = '10.1.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/db_schema_upgrade_10.1.0.6.0.sql';
  ELSIF substr(l_db_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/db_schema_upgrade_10.2.0.9.0.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&db_schema_upgrade

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE pp_schema_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_pp_ver VARCHAR2(16);
BEGIN
  BEGIN
     SELECT version INTO l_pp_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'PP';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_pp_ver := '10.1.0.2.0';
  END;
  IF l_pp_ver = '10.2.0.4.2'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_pp_ver >= '10.1.0.2.0' AND l_pp_ver <= '10.1.0.6.0') OR
        (l_pp_ver >= '10.2.0.0.0' AND l_pp_ver <= '10.2.0.4.2')
  THEN
    :script_name := '&EM_SQL_ROOT/db/pp_schema_upgrade_' || l_pp_ver || '.sql';
  ELSIF substr(l_pp_ver, 1, 7) = '10.1.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/pp_schema_upgrade_10.1.0.6.0.sql';
  ELSIF substr(l_pp_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/pp_schema_upgrade_10.2.0.4.2.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&pp_schema_upgrade

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE pp_db_schema_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_ppdb_ver VARCHAR2(16);
BEGIN
  BEGIN
     SELECT version INTO l_ppdb_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'PP_DB';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ppdb_ver := '10.1.0.2.0';
  END;
  IF l_ppdb_ver = '11.2.0.0.0'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_ppdb_ver >= '10.1.0.2.0' AND l_ppdb_ver <= '10.1.0.6.0') OR
        (l_ppdb_ver >= '10.2.0.1.0' AND l_ppdb_ver <= '10.2.0.9.0') OR
        (l_ppdb_ver >= '11.0.0.0.0' AND l_ppdb_ver <= '11.2.0.0.0')
  THEN
    :script_name := '&EM_SQL_ROOT/db/pp_db_schema_upgrade_' || l_ppdb_ver || '.sql';
  ELSIF substr(l_ppdb_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/pp_db_schema_upgrade_10.2.0.9.0.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&pp_db_schema_upgrade

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE cell_schema_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_cell_ver VARCHAR2(16);
BEGIN
  BEGIN
     SELECT version INTO l_cell_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CELL';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_cell_ver := '10.1.0.2.0';
  END;
  IF l_cell_ver = '11.2.0.0.0'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_cell_ver >= '10.1.0.2.0' AND l_cell_ver <= '10.1.0.6.0') OR
        (l_cell_ver >= '10.2.0.1.0' AND l_cell_ver <= '10.2.0.9.0') OR
        (l_cell_ver >= '11.0.0.0.0' AND l_cell_ver <= '11.2.0.0.0')
  THEN
    :script_name := '&EM_SQL_ROOT/db/cell_schema_upgrade_' || l_cell_ver || '.sql';
  ELSIF substr(l_cell_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/cell_schema_upgrade_10.2.0.9.0.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;
END;
/

SELECT :script_name FROM DUAL;
@&cell_schema_upgrade

-- 
-- Recreate CORE procs
Rem Select core_recreate script to run
Rem
COLUMN :script_name NEW_VALUE core_recreate NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE 
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CORE';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ver := '10.1.0.2.0';
  END;

  IF substr(l_ver, 1, 10) = '10.2.0.4.2' THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/db/core_recreate.sql';
  END IF;

END;
/     
SELECT :script_name FROM DUAL;
  
@&core_recreate

REM - @&EM_SQL_ROOT/db/core_recreate.sql

-- 
-- Recreate DB procs
Rem Select db_recreate script to run
Rem
COLUMN :script_name NEW_VALUE db_recreate NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE 
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'DB';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ver := '10.1.0.2.0';
  END;


	IF substr(l_ver, 1, 10) = '11.2.0.0.0' THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/db/db_recreate.sql';
  END IF;

END;
/     
SELECT :script_name FROM DUAL;
  
@&db_recreate

REM - @&EM_SQL_ROOT/db/db_recreate.sql

-- 
-- Recreate PP procs
Rem Select pp_recreate script to run
Rem
COLUMN :script_name NEW_VALUE pp_recreate NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE 
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'PP';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ver := '10.1.0.2.0';
  END;


	IF substr(l_ver, 1, 10) = '10.2.0.4.2' THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/db/pp_recreate.sql';
  END IF;

END;
/     
SELECT :script_name FROM DUAL;
  
@&pp_recreate

REM - @&EM_SQL_ROOT/db/pp_recreate.sql

-- 
-- Recreate PP_DB procs
Rem Select pp_db_recreate script to run
Rem
COLUMN :script_name NEW_VALUE pp_db_recreate NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE 
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'PP_DB';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ver := '10.1.0.2.0';
  END;


	IF substr(l_ver, 1, 10) = '11.2.0.0.0' THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/db/pp_db_recreate.sql';
  END IF;

END;
/     
SELECT :script_name FROM DUAL;
  
@&pp_db_recreate

-- 
-- Recreate CELL procs
Rem Select cell_recreate script to run
Rem 
COLUMN :script_name NEW_VALUE cell_recreate NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CELL';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ver := '10.1.0.2.0';
  END;

  IF substr(l_ver, 1, 10) = '11.2.0.0.0' THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/db/cell_recreate.sql';
  END IF;

END;
/
SELECT :script_name FROM DUAL;

@&cell_recreate

-- 

-- Recompile all the schema objects
@&EM_SQL_ROOT/core/latest/admin/admin_recompile_invalid.sql &&EM_REPOS_USER

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE core_pre_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_core_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_core_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CORE';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_core_ver := '10.1.0.2.0';
  END;
  IF l_core_ver = '10.2.0.4.2'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_core_ver >= '10.1.0.2.0' AND l_core_ver <= '10.1.0.6.0') OR
        (l_core_ver >= '10.2.0.0.0' AND l_core_ver <= '10.2.0.4.2')
  THEN
    :script_name := '&EM_SQL_ROOT/db/core_pre_data_upgrade_' || l_core_ver || '.sql';
  ELSIF substr(l_core_ver, 1, 7) = '10.1.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/core_pre_data_upgrade_10.1.0.6.0.sql';
  ELSIF substr(l_core_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/core_pre_data_upgrade_10.2.0.4.2.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&core_pre_data_upgrade

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE core_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_core_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_core_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CORE';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_core_ver := '10.1.0.2.0';
  END;
  IF l_core_ver = '10.2.0.4.2'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_core_ver >= '10.1.0.2.0' AND l_core_ver <= '10.1.0.6.0') OR
        (l_core_ver >= '10.2.0.0.0' AND l_core_ver <= '10.2.0.4.2')
  THEN
    :script_name := '&EM_SQL_ROOT/db/core_data_upgrade_' || l_core_ver || '.sql';
  ELSIF substr(l_core_ver, 1, 7) = '10.1.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/core_data_upgrade_10.1.0.6.0.sql';
  ELSIF substr(l_core_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/core_data_upgrade_10.2.0.4.2.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&core_data_upgrade

Rem Select core_post_data_upgrade script to run
Rem
COLUMN :script_name NEW_VALUE core_post_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE 
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CORE';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ver := '10.1.0.2.0';
  END;

  IF substr(l_ver, 1, 10) = '10.2.0.4.2' THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/db/core_post_data_upgrade.sql';
  END IF;

END;
/     
SELECT :script_name FROM DUAL;
  
@&core_post_data_upgrade
      
REM - @&EM_SQL_ROOT/db/core_post_data_upgrade.sql

EXECUTE EMD_MAINTENANCE.SET_VERSION('CORE', '10.2.0.4.2', '10.2.0.4.2', '&EM_REPOS_MODE');

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE db_pre_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_ver VARCHAR2(16);
  l_db_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_db_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'DB';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_db_ver := '10.1.0.2.0';
  END;
  l_ver := substr(l_db_ver,1,8);
  IF l_db_ver = '11.2.0.0.0'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF l_db_ver = '10.2.0.1.0' OR
        l_db_ver = '10.2.0.0.0' 
  THEN
        -- need to use 10.2.0.0.0 since DBControl 10.2.0.1.0 is
        -- the 10.2.0.0.0 DB component version
    :script_name := '&EM_SQL_ROOT/db/db_pre_data_upgrade_10.2.0.0.0.sql';
  ELSIF (l_db_ver >= '10.1.0.2.0' AND l_db_ver <= '10.1.0.6.0') OR
        (l_db_ver >= '10.2.0.2.0' AND l_db_ver <= '10.2.0.9.0') OR
        (l_db_ver >= '11.0.0.0.0' AND l_db_ver <= '11.2.0.0.0')
  THEN
    :script_name := '&EM_SQL_ROOT/db/db_pre_data_upgrade_' || l_db_ver || '.sql';
  ELSIF substr(l_db_ver, 1, 7) = '10.1.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/db_pre_data_upgrade_10.1.0.6.0.sql';
  ELSIF substr(l_db_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/db_pre_data_upgrade_10.2.0.9.0.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&db_pre_data_upgrade

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE db_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_ver VARCHAR2(16);
  l_db_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_db_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'DB';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_db_ver := '10.1.0.2.0';
  END;
  l_ver := substr(l_db_ver,1,8);
  IF l_db_ver = '11.2.0.0.0'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF l_db_ver = '10.2.0.1.0' OR
        l_db_ver = '10.2.0.0.0'
  THEN
        -- need to use 10.2.0.0.0 since DBControl 10.2.0.1.0 is
        -- the 10.2.0.0.0 DB component version
    :script_name := '&EM_SQL_ROOT/db/db_data_upgrade_10.2.0.0.0.sql';
  ELSIF (l_db_ver >= '10.1.0.2.0' AND l_db_ver <= '10.1.0.6.0') OR
        (l_db_ver >= '10.2.0.2.0' AND l_db_ver <= '10.2.0.9.0') OR
        (l_db_ver >= '11.0.0.0.0' AND l_db_ver <= '11.2.0.0.0')
  THEN
    :script_name := '&EM_SQL_ROOT/db/db_data_upgrade_' || l_db_ver || '.sql';
  ELSIF substr(l_db_ver, 1, 7) = '10.1.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/db_data_upgrade_10.1.0.6.0.sql';
  ELSIF substr(l_db_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/db_data_upgrade_10.2.0.9.0.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&db_data_upgrade

Rem Select db_post_data_upgrade script to run
Rem
COLUMN :script_name NEW_VALUE db_post_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE 
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'DB';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ver := '10.1.0.2.0';
  END;

  IF substr(l_ver, 1, 10) = '11.2.0.0.0' THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/db/db_post_data_upgrade.sql';
  END IF;

END;
/     
SELECT :script_name FROM DUAL;
  
@&db_post_data_upgrade
 
REM - @&EM_SQL_ROOT/db/db_post_data_upgrade.sql

EXECUTE EMD_MAINTENANCE.SET_VERSION('DB', '11.2.0.0.0', '11.2.0.0.0', '&EM_REPOS_MODE');


Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE pp_pre_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_pp_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_pp_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'PP';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_pp_ver := '10.1.0.2.0';
  END;
  IF l_pp_ver = '10.2.0.4.2'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_pp_ver >= '10.1.0.2.0' AND l_pp_ver <= '10.1.0.6.0') OR
        (l_pp_ver >= '10.2.0.0.0' AND l_pp_ver <= '10.2.0.4.2')
  THEN
    :script_name := '&EM_SQL_ROOT/db/pp_pre_data_upgrade_' || l_pp_ver ||'.sql';
  ELSIF substr(l_pp_ver, 1, 7) = '10.1.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/pp_pre_data_upgrade_10.1.0.6.0.sql';
  ELSIF substr(l_pp_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/pp_pre_data_upgrade_10.2.0.4.2.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&pp_pre_data_upgrade

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE pp_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_pp_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_pp_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'PP';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_pp_ver := '10.1.0.2.0';
  END;
  IF l_pp_ver = '10.2.0.4.2'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_pp_ver >= '10.1.0.2.0' AND l_pp_ver <= '10.1.0.6.0') OR
        (l_pp_ver >= '10.2.0.0.0' AND l_pp_ver <= '10.2.0.4.2')
  THEN
    :script_name := '&EM_SQL_ROOT/db/pp_data_upgrade_' || l_pp_ver || '.sql';
  ELSIF substr(l_pp_ver, 1, 7) = '10.1.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/pp_data_upgrade_10.1.0.6.0.sql';
  ELSIF substr(l_pp_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/pp_data_upgrade_10.2.0.4.2.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&pp_data_upgrade

Rem Select pp_post_data_upgrade script to run
Rem
COLUMN :script_name NEW_VALUE pp_post_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE 
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'PP';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ver := '10.1.0.2.0';
  END;

  IF substr(l_ver, 1, 10) = '10.2.0.4.2' THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/db/pp_post_data_upgrade.sql';
  END IF;

END;
/     
SELECT :script_name FROM DUAL;
  
@&pp_post_data_upgrade
 
REM - @&EM_SQL_ROOT/db/pp_post_data_upgrade.sql

EXECUTE EMD_MAINTENANCE.SET_VERSION('PP', '10.2.0.4.2', '10.2.0.4.2', '&EM_REPOS_MODE');

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE pp_db_pre_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_ppdb_ver VARCHAR2(16);
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ppdb_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'PP_DB';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ppdb_ver := '10.1.0.2.0';
  END;
  l_ver := substr(l_ppdb_ver, 1, 8);
  IF l_ppdb_ver = '11.2.0.0.0'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_ppdb_ver >= '10.1.0.2.0' AND l_ppdb_ver <= '10.1.0.6.0') OR
        (l_ppdb_ver >= '10.2.0.1.0' AND l_ppdb_ver <= '10.2.0.9.0') OR
        (l_ppdb_ver >= '11.0.0.0.0' AND l_ppdb_ver <= '11.2.0.0.0')
  THEN
    :script_name := '&EM_SQL_ROOT/db/pp_db_pre_data_upgrade_' || l_ppdb_ver || '.sql';
  ELSIF substr(l_ppdb_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/pp_db_pre_data_upgrade_10.2.0.9.0.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&pp_db_pre_data_upgrade

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE pp_db_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_ppdb_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ppdb_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'PP_DB';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ppdb_ver := '10.1.0.2.0';
  END;
  IF l_ppdb_ver = '11.2.0.0.0'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_ppdb_ver >= '10.1.0.2.0' AND l_ppdb_ver <= '10.1.0.6.0') OR
        (l_ppdb_ver >= '10.2.0.1.0' AND l_ppdb_ver <= '10.2.0.9.0') OR
        (l_ppdb_ver >= '11.0.0.0.0' AND l_ppdb_ver <= '11.2.0.0.0')
  THEN
    :script_name := '&EM_SQL_ROOT/db/pp_db_data_upgrade_' || l_ppdb_ver || '.sql';
  ELSIF substr(l_ppdb_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/pp_db_data_upgrade_10.2.0.9.0.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&pp_db_data_upgrade

Rem Select pp_db_post_data_upgrade script to run
Rem
COLUMN :script_name NEW_VALUE pp_db_post_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE 
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'PP_DB';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ver := '10.1.0.2.0';
  END;

  IF substr(l_ver, 1, 10) = '11.2.0.0.0' THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/db/pp_db_post_data_upgrade.sql';
  END IF;

END;
/     
SELECT :script_name FROM DUAL;
  
@&pp_db_post_data_upgrade
 
REM - @&EM_SQL_ROOT/db/pp_db_post_data_upgrade.sql

EXECUTE EMD_MAINTENANCE.SET_VERSION('PP_DB', '11.2.0.0.0', '11.2.0.0.0', '&EM_REPOS_MODE');

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE cell_pre_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_cell_ver VARCHAR2(16);
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_cell_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CELL';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_cell_ver := '10.1.0.2.0';
  END;
  IF l_cell_ver = '11.2.0.0.0'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_cell_ver >= '10.1.0.2.0' AND l_cell_ver <= '10.1.0.6.0') OR
        (l_cell_ver >= '10.2.0.1.0' AND l_cell_ver <= '10.2.0.9.0') OR
        (l_cell_ver >= '11.0.0.0.0' AND l_cell_ver <= '11.2.0.0.0')
  THEN
    :script_name := '&EM_SQL_ROOT/db/cell_pre_data_upgrade_' || l_cell_ver || '.sql';
  ELSIF substr(l_cell_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/cell_pre_data_upgrade_10.2.0.9.0.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&cell_pre_data_upgrade

Rem Select upgrade script to run
Rem
COLUMN :script_name NEW_VALUE cell_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_cell_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_cell_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CELL';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_cell_ver := '10.1.0.2.0';
  END;
  IF l_cell_ver = '11.2.0.0.0'
  THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSIF (l_cell_ver >= '10.1.0.2.0' AND l_cell_ver <= '10.1.0.6.0') OR
        (l_cell_ver >= '10.2.0.1.0' AND l_cell_ver <= '10.2.0.9.0') OR
        (l_cell_ver >= '11.0.0.0.0' AND l_cell_ver <= '11.2.0.0.0')
  THEN
    :script_name := '&EM_SQL_ROOT/db/cell_data_upgrade_' || l_cell_ver || '.sql';
  ELSIF substr(l_cell_ver, 1, 7) = '10.2.0.' THEN
    :script_name := '&EM_SQL_ROOT/db/cell_data_upgrade_10.2.0.9.0.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  END IF;

END;
/

SELECT :script_name FROM DUAL;
@&cell_data_upgrade

Rem Select cell_post_data_upgrade script to run
Rem
COLUMN :script_name NEW_VALUE cell_post_data_upgrade NOPRINT
VARIABLE script_name VARCHAR2(256)

DECLARE
  l_ver VARCHAR2(16);
BEGIN
  BEGIN
    SELECT version INTO l_ver
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CELL';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_ver := '10.1.0.2.0';
  END;

  IF substr(l_ver, 1, 10) = '11.2.0.0.0' THEN
    :script_name := '&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
  ELSE
    :script_name := '&EM_SQL_ROOT/db/cell_post_data_upgrade.sql';
  END IF;

END;
/
SELECT :script_name FROM DUAL;

@&cell_post_data_upgrade

EXECUTE EMD_MAINTENANCE.SET_VERSION('CELL', '11.2.0.0.0', '11.2.0.0.0', '&EM_REPOS_MODE');


-- Resubmit the new DBMS jobs..
@&EM_SQL_ROOT/core/latest/admin/admin_submit_dbms_jobs.sql


-- Mark the dbms_registry for EM component as upgraded.
DECLARE
  l_comp_name VARCHAR2(256);
BEGIN
  l_comp_name := 'EM';
  IF NOT ('&EM_REPOS_MODE' = 'CENTRAL') THEN
    EXECUTE IMMEDIATE 'BEGIN dbms_registry.upgraded(:1); dbms_registry.valid(:2); END;' USING l_comp_name, l_comp_name;
  END IF;
END;
/

-- Reset the session back to SYS
ALTER SESSION SET CURRENT_SCHEMA=SYS;