Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_init_util_procs.sql rbali_oneoff_updated/1 2025/03/14 06:41:15 rbali Exp $
Rem
Rem admin_init_util_procs.sql
Rem
Rem Copyright (c) 2025, Oracle and/or its affiliates.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_init_util_procs.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_init_util_procs.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      admin_init_util_procs.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem     This is used only for install path, for upgrade path
Rem
Rem    BEGIN SQL_FILE_METADATA
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_init_util_procs.sql
Rem    SQL_SHIPPED_FILE:
Rem    SQL_PHASE:
Rem    SQL_STARTUP_MODE: NORMAL
Rem    SQL_IGNORABLE_ERRORS: NONE
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gbafna      02/05/25 - add cutover synonyms procedure
Rem    gbafna      02/05/25 - Created
Rem

DEFINE MY_REPOS_USER = "&1"
DEFINE MY_DBA_USER = "&2"

ALTER SESSION SET CURRENT_SCHEMA=&MY_REPOS_USER;

CREATE OR REPLACE PROCEDURE CUTOVER_SYNONYM(p_synonym_name VARCHAR2) IS
    l_synonym_exists NUMBER;
    l_error_message VARCHAR2(4000);
    l_component_name VARCHAR2(4000);
    l_log_message_id VARCHAR2(4000);
BEGIN
    SELECT COUNT(*) INTO l_synonym_exists
    FROM DBA_SYNONYMS 
    WHERE SYNONYM_NAME = UPPER(p_synonym_name)
    AND OWNER = 'SYSMAN' 
    AND EXISTS (
        SELECT OBJECT_NAME 
        FROM DBA_OBJECTS 
        WHERE OBJECT_TYPE = 'SYNONYM' 
        AND OBJECT_NAME = UPPER(p_synonym_name) 
        AND EDITION_NAME IS NULL
    );

    l_component_name := 'ADMIN_CUTOVER_SYNONYM_PROCEDURE';
    l_log_message_id := 'ADMIN_CUTOVER_SYNONYM_PROCEDURE_ID';
    IF l_synonym_exists > 0 THEN
        MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, l_component_name, l_log_message_id, p_synonym_name || ' synonym already exists so no action taken.');                    
    ELSE
        BEGIN
            EXECUTE IMMEDIATE 'DROP SYNONYM SYSMAN.' || p_synonym_name || ' FORCE';
             MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, l_component_name, l_log_message_id, p_synonym_name || ' synonym dropped using CUTOVER_SYNONYM procedure.');                    
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE != -1434 THEN
                    RAISE;
                END IF;
        END;
        EXECUTE IMMEDIATE 'CREATE NONEDITIONABLE SYNONYM SYSMAN.' || p_synonym_name || ' FOR SYSMAN_TYPES.' || p_synonym_name;
        MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, l_component_name, l_log_message_id, p_synonym_name || ' synonym recreated using CUTOVER_SYNONYM procedure.');                    
    END IF;    

EXCEPTION
    WHEN OTHERS THEN
        l_error_message := 'Encountered error when dropping and creating synonym ' || p_synonym_name || ': ' || SQLERRM;
        MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, l_component_name, l_log_message_id, l_error_message);
        RAISE;                
END CUTOVER_SYNONYM;
/

ALTER SESSION SET CURRENT_SCHEMA=&MY_DBA_USER;
