Rem DO NOT PUT HEADER HERE.  THIS IS CALLED DIRECTLY BY empatch.sql FOR
Rem DBControl schema upgrade
Rem
Rem $Header: pre_upgrade_heuristics.sql645 28-nov-2006.15:26:09 chyu Exp $
Rem
Rem pre_upgrade_heuristics.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      pre_upgrade_heuristics.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        11/28/06 - adding the heuristics for upgrade
Rem    chyu        11/28/06 - Created
Rem

Rem using the heuristics to determine the appropriate
Rem DB component version
Rem The problem is that DB component has 10.1.0.4 as the version
Rem in both RBDMS 10.1.0.4 and 10.2.0.1.  That is a serious
Rem issue since there is no way to have appropriate upgrade
Rem paths for both version differently.
Rem Thus, before running any upgrade script, do the following
Rem    1. check the DB component version, if it is 10.1.0.4
Rem    2. then, check whether or not the table 
Rem       MGMT_OB_ADMIN_HOSTS
Rem       exists.  if it does, reset DB component version to
Rem       10.2.0.1

DECLARE
  l_ver VARCHAR2(16);
  l_count NUMBER := 0;
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';
  END;
  IF substr(l_ver, 1, 8) = '10.1.0.4' THEN
    BEGIN
      SELECT count(*) INTO l_count
      FROM ALL_TABLES
      WHERE upper(table_name)='MGMT_OB_ADMIN_HOSTS'
        AND upper(owner) = upper ('&EM_REPOS_USER');

      IF (l_count = 1) THEN
        BEGIN
          EMD_MAINTENANCE.SET_VERSION('DB', '10.2.0.1.0', '10.2.0.1.0', '&EM_REPOS_MODE');
          COMMIT;
        END;
      END IF;
    END;
  END IF;
END;
/