Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_editionable_objects_actualization.sql rbali_oneoff_updated/1 2025/03/14 06:41:13 rbali Exp $
Rem
Rem admin_editionable_objects_actualization.sql
Rem
Rem Copyright (c) 2024, 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_editionable_objects_actualization.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_editionable_objects_actualization.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_editionable_objects_actualization.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_editionable_objects_actualization.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    akrasto     02/09/25 - logging fix
Rem    akrasto     01/28/25 - actualization cutover to actualize_all
Rem    akrasto     08/21/24 - error logging
Rem    akrasto     07/13/24 - actualization optimization
Rem    akrasto     05/04/24 - actualization
Rem    akrasto     05/04/24 - Created
Rem

DEFINE RECOMPILE_REPOS_USER = "&1"
DEFINE IS_PLATFORM_UPGRADE = "&2"

DECLARE
l_max_actualization_attempts NUMBER := 3; --
l_actualization_attempts NUMBER := 0; --
l_object_count NUMBER := 0; --
-- l_synonym_count NUMBER := 0;
l_sql VARCHAR2(2000);
l_error_message VARCHAR2(2000);
l_warning_message VARCHAR2(2000);
l_platform_upg_status VARCHAR2(50) := '&IS_PLATFORM_UPGRADE';
l_current_edition VARCHAR2(100);
l_count_actualized INTEGER;
l_em_ne_synonyms_cutover_count INTEGER;
l_actualize_all_used_count INTEGER;
l_actualize_all_use_edition_count INTEGER;
BEGIN
    l_current_edition := SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME');
    SELECT COUNT(*) INTO l_count_actualized FROM SYSMAN.EM_ACTUALIZATION_STATE WHERE EDITION_NAME=l_current_edition AND STATUS = 'DONE';
    SELECT COUNT(*) INTO l_em_ne_synonyms_cutover_count FROM SYSMAN.MGMT_LCM_PROPERTIES WHERE PROPERTY_NAME='EM_NE_SYNONYMS_CUTOVER_DONE' AND PROPERTY_VALUE NOT LIKE '%FALSE%';
    SELECT COUNT(*) INTO l_actualize_all_used_count FROM SYSMAN.MGMT_LCM_PROPERTIES WHERE PROPERTY_NAME=l_current_edition || ':ACTUALIZE_ALL_USED' AND PROPERTY_VALUE='TRUE';
    
    IF (l_em_ne_synonyms_cutover_count > 0) THEN
        INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'SYSMAN_TYPES Synonyms cutover to Non-editionable done. Checking for actualize_all usage.', sysdate);
        COMMIT;
    ELSE
        INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'SYSMAN_TYPES Synonyms are not cutover to Non-editionable.', sysdate);
        COMMIT;
    END IF;

    IF (l_actualize_all_used_count > 0) THEN
        INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'actualize_all already used so all schemas are actualized.', sysdate);
        COMMIT;
    END IF;
    
    IF (l_current_edition = 'ORA$BASE') THEN
        INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'Skipping Actualization because current edition: ' || l_current_edition  || ' for schema &RECOMPILE_REPOS_USER.', sysdate);
        COMMIT;
    END IF;
    IF (l_count_actualized = 1 AND l_actualize_all_used_count = 0) THEN
        INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'Skipping Actualization because current edition: ' || l_current_edition || ' is already actualized for schema &RECOMPILE_REPOS_USER.', sysdate);
        COMMIT;
    END IF;
    IF(l_platform_upg_status = 'TRUE' AND l_current_edition != 'ORA$BASE' AND l_count_actualized = 0) THEN
        IF(l_em_ne_synonyms_cutover_count>0 and l_actualize_all_used_count=0) THEN
            INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'Starting Actualization on edition: '|| l_current_edition || ' using actualize_all for all schemas.' , sysdate);
            COMMIT;
            BEGIN
                dbms_editions_utilities2.actualize_all();
            END;
            INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'Completed Actualization on edition: '|| l_current_edition || ' using actualize_all for all schemas.' , sysdate);
            COMMIT;
            BEGIN
                SELECT COUNT(*) INTO l_actualize_all_use_edition_count FROM SYSMAN.MGMT_LCM_PROPERTIES WHERE PROPERTY_NAME like '%:ACTUALIZE_ALL_USED%';
                IF (l_actualize_all_use_edition_count > 0) THEN
                    INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'Updating ACTUALIZE_ALL_USED flag TRUE for edition: ' || l_current_edition , sysdate);
                    COMMIT;
                    UPDATE SYSMAN.MGMT_LCM_PROPERTIES
                        SET PROPERTY_NAME = l_current_edition || ':ACTUALIZE_ALL_USED'
                        WHERE PROPERTY_NAME like '%:ACTUALIZE_ALL_USED%';
                ELSE 
                    INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'Inserting ACTUALIZE_ALL_USED flag TRUE for edition: '  || l_current_edition  , sysdate);
                    COMMIT;
                    INSERT INTO SYSMAN.MGMT_LCM_PROPERTIES (PROPERTY_NAME, PROPERTY_VALUE) VALUES (l_current_edition || ':ACTUALIZE_ALL_USED', 'TRUE');
                    COMMIT;
                END IF;
            EXCEPTION
                WHEN OTHERS THEN
                    RAISE;
            END;
        END IF;
        
        
        
        IF (l_em_ne_synonyms_cutover_count = 0) THEN
            INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'Starting Actualization on edition: ' || l_current_edition || ' for schema &RECOMPILE_REPOS_USER using individual Schema compilation.'  , sysdate);
            COMMIT;
            BEGIN
                SELECT COUNT(*)
                INTO l_object_count
                FROM dba_objects
                WHERE owner = upper('&RECOMPILE_REPOS_USER')
                AND (object_type IN ('FUNCTION', 'PACKAGE',
                                    'PACKAGE BODY', 'PROCEDURE',
                                    'TRIGGER', 'VIEW',
                                    'TYPE BODY', 'MATERIALIZED VIEW')
                    OR (object_type = 'TYPE' AND subobject_name IS NULL));
                    INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_NAME_ID', 'Starting object compilation for Actualization for schema &RECOMPILE_REPOS_USER.', sysdate);
                    COMMIT;
                WHILE (l_object_count > 0 AND
                            l_actualization_attempts < l_max_actualization_attempts)
                LOOP
                -- Use the built in RDBMS recompile first.
                -- dbms_utility.compile_schema(upper('&RECOMPILE_REPOS_USER'),FALSE);

                    FOR crec IN (SELECT DISTINCT object_name, object_type, owner,
                                DECODE(object_type, 'TYPE',              1,
                                                    'FUNCTION',          2,
                                                    'PROCEDURE',         3,
                                                    'TRIGGER',           4,
                                                    'VIEW',              5,
                                                    'PACKAGE',           6,
                                                    'TYPE BODY',         7,
                                                    'PACKAGE BODY',      8,
                                                    'MATERIALIZED VIEW', 9,
                                                                        10) object_type_seq
                                FROM dba_objects_ae
                                WHERE owner = upper('&RECOMPILE_REPOS_USER')
                                    AND (object_type IN ('FUNCTION', 'PACKAGE',
                                                        'PACKAGE BODY', 'PROCEDURE',
                                                        'TRIGGER', 'VIEW', 'TYPE BODY',
                                                        'MATERIALIZED VIEW')
                                        OR (object_type = 'TYPE' AND subobject_name IS NULL))
                                ORDER BY object_type_seq, object_type, object_name, owner)
                        LOOP
                            DECLARE
                                l_object_exists_in_current_edition NUMBER;
                                l_object_exists_in_repository NUMBER;
                            BEGIN
                                SELECT COUNT(*)
                                INTO l_object_exists_in_current_edition
                                FROM dba_objects_ae
                                WHERE object_name = crec.object_name
                                AND object_type = crec.object_type
                                AND owner = crec.owner
                                AND EDITION_NAME=SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME');

                                SELECT COUNT(*)
                                INTO l_object_exists_in_repository
                                FROM SYSMAN.EM_EDITIONED_OBJECTS
                                WHERE object_name = crec.object_name
                                AND object_type = crec.object_type
                                AND owner = crec.owner;
                            
                            IF l_object_exists_in_current_edition = 0 AND l_object_exists_in_repository = 0 THEN
                                l_warning_message := 'Object with object name ' || crec.object_name || ', object type ' || crec.object_type ||' and owner ' || crec.owner || ' is present in previous editions (dba_objects_ae) but not present in repository.';
                                INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', l_warning_message, sysdate);
                                COMMIT;
                            END IF;
                            
                            IF l_object_exists_in_current_edition = 0 AND l_object_exists_in_repository != 0 THEN
                                IF (crec.object_type like '%PACKAGE%BODY%') THEN
                                    -- If package body is invalid, just compile the body and not
                                    -- the specification
                                l_sql := 'ALTER PACKAGE "' ||
                                                    crec.object_name || '" compile body';
                                EXECUTE IMMEDIATE l_sql;
                                ELSIF (crec.object_type = 'TYPE')
                                THEN
                                -- If type spec is invalid, just compile the specification and not
                                -- the body
                                l_sql := 'ALTER TYPE "' ||
                                                crec.object_name || '" compile specification';
                                EXECUTE IMMEDIATE l_sql;
                                ELSIF (crec.object_type = 'TYPE BODY')
                                THEN
                                -- If type body is invalid, just compile the body and not
                                -- the specification
                                l_sql := 'ALTER TYPE "' ||
                                                crec.object_name || '" compile body';
                                EXECUTE IMMEDIATE l_sql;
                                ELSE
                                l_sql := 'ALTER ' || crec.object_type || ' "' ||
                                                crec.object_name || '" compile';
                                EXECUTE IMMEDIATE l_sql;
                                END IF;
                            END IF;
                            EXCEPTION
                            WHEN OTHERS THEN
                                l_error_message := TO_CHAR(SQLCODE) ||  ' -Error ' || TO_CHAR(SQLERRM);
                                INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ERROR_ID', 'Error while executing: ' || l_sql || ', error message: ' || l_error_message, sysdate);
                                COMMIT;
                            END;
                            

                        END LOOP;
                        -- For loop recompile

                    SELECT COUNT(*)
                        INTO l_object_count
                        FROM dba_objects
                        WHERE owner = upper('&RECOMPILE_REPOS_USER')
                        AND (object_type IN ('FUNCTION', 'PACKAGE',
                                            'PACKAGE BODY', 'PROCEDURE',
                                            'TRIGGER', 'VIEW',
                                            'TYPE BODY', 'MATERIALIZED VIEW')
                            OR (object_type = 'TYPE' AND subobject_name IS NULL));

                    l_actualization_attempts := l_actualization_attempts + 1;
                END LOOP;
                -- While loop
                INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'Compilation for Actualization complete for Schema &RECOMPILE_REPOS_USER.', sysdate);
                COMMIT;
                    -- drop and recreate invalid private synonyms pointing to repository owner
                    -- to make them valid.
                INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'Starting SYNONYMS Actualization for schema &RECOMPILE_REPOS_USER.', sysdate);
                COMMIT;
                FOR rec IN ( SELECT s.owner, s.table_owner, s.table_name, s.synonym_name
                    FROM dba_synonyms s
                    JOIN dba_objects_ae o
                    ON s.synonym_name = o.object_name
                    AND s.owner = o.owner
                    WHERE s.owner IN ('SYSMAN', 'SYSMAN_RO', 'MGMT_VIEW', 'SYSMAN_ZDTUSER')
                    AND o.object_type = 'SYNONYM'
                    AND o.edition_name IN (SELECT DISTINCT edition_name FROM dba_objects_ae)
                    AND s.table_owner != 'SYSMAN_TYPES')
                LOOP
                    DECLARE
                        l_synonym_exists_in_current_edition NUMBER;
                        l_synonym_exists_in_repository NUMBER;
                    BEGIN
                        SELECT COUNT(*)
                        INTO l_synonym_exists_in_current_edition
                        FROM dba_objects_ae
                        WHERE object_name = rec.synonym_name
                        AND object_type = 'SYNONYM'
                        AND owner = rec.owner
                        AND EDITION_NAME=SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME');

                        SELECT COUNT(*)
                        INTO l_synonym_exists_in_repository
                        FROM SYSMAN.EM_EDITIONED_OBJECTS
                        WHERE object_name = rec.synonym_name
                        AND object_type = 'SYNONYM'
                        AND owner = rec.owner;

                        IF l_synonym_exists_in_current_edition = 0 AND l_synonym_exists_in_repository = 0 THEN
                            l_warning_message := 'Object with object name ' || rec.synonym_name || ' object type SYNONYM and owner ' || rec.owner || ' is present in previous editions (dba_objects_ae) but not present in repository';
                            INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', l_warning_message, sysdate);
                            COMMIT;
                        END IF;
                        IF l_synonym_exists_in_current_edition = 0 AND l_synonym_exists_in_repository != 0 THEN
                            l_sql := 'DROP SYNONYM '||rec.owner||'.'||rec.synonym_name;
                            EXECUTE IMMEDIATE l_sql;
                            l_sql := 'CREATE OR REPLACE SYNONYM '||rec.owner||'.'||rec.synonym_name
                                            ||' FOR '|| rec.table_owner||'.'||rec.table_name ;
                            EXECUTE IMMEDIATE l_sql;

                        --select count(*) into l_synonym_count from dba_objects_ae where owner in ('SYSMAN', 'SYSMAN_RO', 'SYSMAN_TYPES', 'MGMT_VIEW') and object_type in ( select object_type from dba_objects where editionable='Y') and edition_name='EM135RU5RPU2' and object_type in ('SYNONYM');
                        --dbms_output.put_line('Object count ' || l_synonym_count); 
                        END IF;
                        EXCEPTION
                        WHEN OTHERS THEN 
                            l_error_message := TO_CHAR(SQLCODE) ||  ' -Error ' || TO_CHAR(SQLERRM);
                            INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) values (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_SYNONYM_ACTUALIZATION_ERROR_ID', 'Error while executing: ' || l_sql || ', error message: ' || l_error_message, sysdate);
                            COMMIT;
                    END;
                END LOOP;
                INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'Ending SYNONYMS Actualization for schema &RECOMPILE_REPOS_USER.', sysdate);
                COMMIT;
            END;
            INSERT INTO SYSMAN.MGMT_EMDEP_LOGS(log_level, component_name, log_message_id, log_message, logging_time) VALUES (0, 'ADMIN_ACTUALIZE_OBJECT', 'ADMIN_ACTUALIZE_OBJECT_ID', 'Ending Actualization on edition: ' || l_current_edition || ' for schema &RECOMPILE_REPOS_USER.'  , sysdate);
            COMMIT;
        END IF;
    END IF;
END;
/

