Rem
Rem $Header: emrelod.sql 11-apr-2008.07:48:09 chyu Exp $
Rem
Rem emrelod.sql
Rem
Rem Copyright (c) 2004, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      emrelod.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    chyu        04/11/08 - upping the version for RDBMS 11.2
Rem    chyu        10/25/06 - using the _recreate.sql instead of _procs.sql
Rem                           script
Rem    rpinnama    09/23/04 - 
Rem    rpinnama    08/26/04 - rpinnama_add_upgrade_downgrade_scripts
Rem    rpinnama    05/06/04 - rpinnama_support_downgrade
Rem    rpinnama    05/05/04 - Created
Rem


define EMDW_HOME                       = "?"
define EM_REPOS_USER                   = "SYSMAN"

define EM_SQL_ROOT                     = "&EMDW_HOME/sysman/admin/emdrep/sql"

--
-- Get EM_REPOS_MODE
COLUMN :rep_mode NEW_VALUE EM_REPOS_MODE NOPRINT
VARIABLE rep_mode VARCHAR2(256)

DECLARE
  l_rep_mode VARCHAR2(28);
BEGIN
  BEGIN
    SELECT component_mode INTO l_rep_mode
      FROM &&EM_REPOS_USER..MGMT_VERSIONS
     WHERE component_name = 'CORE';
  EXCEPTION
    WHEN OTHERS THEN
      -- Default to CENTRAL mode
      l_rep_mode := 'CENTRAL';
  END;

  :rep_mode := l_rep_mode;
END;
/

SELECT :rep_mode FROM DUAL;

-- Contine on SQL errors
WHENEVER SQLERROR CONTINUE;

ALTER SESSION SET CURRENT_SCHEMA=&&EM_REPOS_USER;

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

--
-- Recreate CORE procs
@&EM_SQL_ROOT/db/core_recreate.sql

--
-- Recreate DB procs
@&EM_SQL_ROOT/db/db_recreate.sql

-- Recreate PP procs
@&EM_SQL_ROOT/db/pp_recreate.sql

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


EXECUTE EMD_MAINTENANCE.SET_VERSION('CORE', '10.2.0.4.2', '10.2.0.4.2', '&&EM_REPOS_MODE');
EXECUTE EMD_MAINTENANCE.SET_VERSION('DB', '11.2.0.0.0', '11.2.0.0.0', '&&EM_REPOS_MODE');
EXECUTE EMD_MAINTENANCE.SET_VERSION('PP', '10.2.0.4.2', '10.2.0.4.2', '&&EM_REPOS_MODE');
EXECUTE EMD_MAINTENANCE.SET_VERSION('PP_DB', '11.2.0.0.0', '11.2.0.0.0', '&&EM_REPOS_MODE');

-- Mark the dbms_registry for EM component as loaded
DECLARE
  l_comp_name VARCHAR2(256);
BEGIN
  l_comp_name := 'EM';
  IF NOT ('&EM_REPOS_MODE' = 'CENTRAL') THEN
    EXECUTE IMMEDIATE 'BEGIN dbms_registry.loaded(: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;