Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/12.1.0.3/adminmsg/adminmsg_bus_types_upgrade.sql rbali_oneoff_updated/1 2025/03/14 06:41:13 rbali Exp $
Rem
Rem adminmsg_bus_types_upgrade.sql
Rem
Rem Copyright (c) 2013, 2025, Oracle and/or its affiliates. 
Rem All rights reserved.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/12.1.0.3/adminmsg/adminmsg_bus_types_upgrade.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/12.1.0.3/adminmsg/adminmsg_bus_types_upgrade.sql 

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      adminmsg_bus_types_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    gbafna      01/07/25 - make synonyms noneditionable
Rem    kalchand    04/08/15 - Type Dependency and Referenced Types Synonyms Dropped
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    srpuluku    04/01/13 - Created

DECLARE
  TYPE l_dependency IS TABLE OF VARCHAR2(30);
  l_type_dependency l_dependency := l_dependency();
  type_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(type_exception, -22324);
BEGIN
   EXECUTE IMMEDIATE 'DROP SYNONYM MGMT_ADMINMSG_NAMEVAL_ARRAY';
   EXECUTE IMMEDIATE q'[select DISTINCT(NAME) FROM DBA_DEPENDENCIES start with REFERENCED_NAME= 'MGMT_ADMINMSG_NAMEVAL_ARRAY' and TYPE='TYPE' CONNECT BY NOCYCLE PRIOR NAME = REFERENCED_NAME and TYPE = 'TYPE']' BULK COLLECT INTO l_type_dependency;
   IF l_type_dependency.COUNT > 0 THEN
     FOR i IN l_type_dependency.first .. l_type_dependency.last LOOP
     BEGIN
        EXECUTE IMMEDIATE 'DROP SYNONYM ' || l_type_dependency(i);
        EXCEPTION
        WHEN OTHERS THEN
        IF (SQLCODE = -1434) THEN
          CONTINUE;
        ELSE
          SYSMAN.MGMT_EMDEP_LOGGER.log_message(1, 'EBR_UPGRADE', 'Failed to Drop Synonym ' || l_type_dependency(i), 'Error : ' || SQLERRM);
          RAISE;
        END IF;
     END;
     END LOOP;
   END IF;

   BEGIN
     execute immediate 'ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES';
     execute immediate 'ALTER TYPE MGMT_ADMINMSG_NAMEVAL_ARRAY MODIFY LIMIT 2000 CASCADE';
     execute immediate 'ALTER SESSION SET CURRENT_SCHEMA=SYSMAN';
     execute immediate 'CREATE OR REPLACE NONEDITIONABLE SYNONYM MGMT_ADMINMSG_NAMEVAL_ARRAY FOR SYSMAN_TYPES.MGMT_ADMINMSG_NAMEVAL_ARRAY';
     EXCEPTION
       WHEN type_exception THEN
       -- If the type is already increased to 100000, ALTER will fail
       -- with compilation error - in this case, the exception is 
       -- acceptable and nothing needs to be done
       NULL;
       WHEN OTHERS THEN
       RAISE;
   END;

   FOR i IN l_type_dependency.first .. l_type_dependency.last LOOP
     EXECUTE IMMEDIATE 'CREATE OR REPLACE NONEDITIONABLE SYNONYM ' || l_type_dependency(i) || ' FOR SYSMAN_TYPES.' || l_type_dependency(i);
   END LOOP;
END;
/


