Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_post_cutover_synonym_to_ne.sql rbali_oneoff_updated/1 2025/03/14 06:41:15 rbali Exp $
Rem
Rem ADMIN_CUTOVER_SYNONYM_to_ne.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_post_cutover_synonym_to_ne.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_post_cutover_synonym_to_ne.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_CUTOVER_SYNONYM_to_ne.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    BEGIN SQL_FILE_METADATA
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/ADMIN_CUTOVER_SYNONYM_to_ne.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      03/10/25 - Created
Rem

DEFINE DBADMIN_USER = "&1"
DEFINE EM_REPOS_USER =  "&2"

ALTER SESSION SET CURRENT_SCHEMA = &EM_REPOS_USER;

DECLARE
    l_sql VARCHAR2(2000);
    l_error_message VARCHAR2(2000);
    l_flag_state VARCHAR2(256);
    l_final_ne_synonym_check NUMBER;
BEGIN
    SELECT PROPERTY_VALUE
        INTO l_flag_state
        FROM SYSMAN.MGMT_LCM_PROPERTIES_E
        WHERE PROPERTY_NAME = 'EM_NE_SYNONYMS_CUTOVER_DONE';

    IF l_flag_state NOT LIKE '%FALSE%' THEN      
        FOR rec IN ( SELECT UNIQUE s.OWNER, s.SYNONYM_NAME, s.TABLE_OWNER, s.TABLE_NAME
                            FROM SYSMAN.EM_NECUTOVER_SYNONYMS_E s
                            JOIN DBA_OBJECTS o
                            ON s.owner = o.owner
                            AND s.synonym_name = o.object_name
                            AND o.object_type = 'SYNONYM'
                            WHERE o.EDITION_NAME IS NOT NULL)                    
        LOOP
            l_sql := 'CREATE NONEDITIONABLE SYNONYM '||rec.owner||'.'||rec.synonym_name||' FOR '|| rec.table_owner||'.'||rec.table_name ;
            BEGIN
                EXECUTE IMMEDIATE l_sql;
                MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'ADMIN_CUTOVER_SYNONYM','ADMIN_CUTOVER_SYNONYM_ID', 'Creating non-editionable synonym using SQL statement: ' || l_sql);                    
            EXCEPTION
                WHEN OTHERS THEN    
                    l_error_message := 'Error occurred, exception: ' || SQLCODE || ' - ' || SQLERRM;
                    MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'ADMIN_CUTOVER_SYNONYM','ADMIN_CUTOVER_SYNONYM_ID', 'Failed to create non-editionable using SQL statement: '|| l_sql || 'Error Message: ' || l_error_message);  
                    RAISE;                  
            END;
        END LOOP;                  
    ELSE
        MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'ADMIN_CUTOVER_SYNONYM','ADMIN_CUTOVER_SYNONYM_ID','Flag: ' || l_flag_state || ' ,EM_NE_SYNONYMS_CUTOVER_DONE has false value, per edition cutover not done properly.');                       
    END IF; 

    SELECT count(*) 
        INTO l_final_ne_synonym_check
        FROM DBA_SYNONYMS s
        JOIN DBA_OBJECTS o 
        ON s.owner=o.owner
        AND s.synonym_name=o.object_name
        AND o.object_type='SYNONYM'
        WHERE EDITION_NAME IS NOT NULL
        AND s.TABLE_OWNER='SYSMAN_TYPES'; 
    IF l_final_ne_synonym_check > 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Some synonyms are still editionable thus raising error after post cutover.'); 
    END IF;

EXCEPTION  
    WHEN OTHERS THEN    
        l_error_message := 'Error occurred, exception block 1: ' || SQLCODE || ' - ' || SQLERRM;
        MGMT_EMDEP_LOGGER.log_message(MGMT_EMDEP_LOGGER.G_LOG_LEVEL_INFO, 'ADMIN_CUTOVER_SYNONYM','ADMIN_CUTOVER_SYNONYM_ID', l_error_message);                     
        RAISE;

END;
/

ALTER SESSION SET CURRENT_SCHEMA = &DBADMIN_USER;