1. Multi-Tenant Patch-set Deployment
  2. Annexure

A Annexure

01_Start_Upgrade.sql

SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application Upgrade initiation
SPOOL "&SPOOL_PATH"

DECLARE
    l_app_name    VARCHAR2(128);
    l_app_currver VARCHAR2(30);
    l_Sql         VARCHAR2(256);
BEGIN
    
    BEGIN
        SELECT app_name
          INTO l_app_name
          FROM dba_applications 
         WHERE app_implicit <> 'Y'
         AND app_name = (SELECT param_val FROM cstb_param WHERE Param_name = 'MULTI_TENANT_APP_NAME');
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Error1 Nodata--->'||SQLERRM);
        WHEN OTHERS THEN
            dbms_output.put_line('Error1 others--->'||SQLERRM);
    END; 
    
    BEGIN
        SELECT MAX(app_version)
          INTO l_app_currver
          FROM dba_app_versions
         WHERE app_name = l_app_name;
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Error2 Nodata--->'||SQLERRM);
        WHEN OTHERS THEN
            dbms_output.put_line('Error2 others--->'||SQLERRM);
    END;
    
    l_Sql := 'ALTER PLUGGABLE DATABASE APPLICATION ' || l_app_name||' BEGIN UPGRADE '''|| l_app_currver || ''' TO '''|| '&P_APPLICATION_NEXTVER' ||'''';
    dbms_output.put_line('l_sql: ' || l_Sql);                             
    EXECUTE IMMEDIATE l_Sql;
    
    l_Sql := 'ALTER SYSTEM SET DEFAULT_SHARING = NONE';
    dbms_output.put_line('l_sql: ' || l_Sql);
    EXECUTE IMMEDIATE l_Sql;
         
EXCEPTION 
    WHEN OTHERS THEN
        dbms_output.put_line('Error --->'||SQLERRM);
END;
/

SET ERRORLOGGING OFF
SPOOL OFF

03_Invalids_Recompilation_Inside_Upgrade.sql

/*  Script for Shared Application + Shared Data */
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application Upgrade Invalids Recompilation
SPOOL "&SPOOL_PATH"

DECLARE
    inval_cnt         NUMBER := 0;
    l_object_name     VARCHAR2(240);
BEGIN
    WHILE inval_cnt < 3 LOOP
        --SCRIPT
        FOR J IN (Select 'alter ' || object_type || ' ' || object_name ||' compile' invalidobject1,
                  object_name
                FROM user_objects 
                WHERE status = 'INVALID'
                AND created_appid IS NOT NULL
                AND object_type IN ('VIEW','SYNONYM','PROCEDURE','FUNCTION','PACKAGE','TRIGGER','MATERIALIZED VIEW'))
        LOOP
            BEGIN
                l_object_name := j.object_name;
                dbms_output.put_line(chr(10));
                EXECUTE IMMEDIATE J.invalidobject1;
            EXCEPTION
                WHEN OTHERS THEN
                    dbms_output.put_line('failed for -->' || l_object_name);
            END;
        END LOOP;
        inval_cnt := inval_cnt + 1;
    END LOOP;
EXCEPTION
  WHEN OTHERS THEN
        dbms_output.put_line('FAILED FOR -->' || l_object_name);
END;
/
DECLARE
    inval_cnt1         NUMBER := 0;
    l_object_name     VARCHAR2(240);
BEGIN
    WHILE inval_cnt1 < 3 LOOP
        --SCRIPT
        FOR k IN (Select 'alter package '|| object_name||' compile body' invalidobject2,
                 object_name
                FROM user_objects 
                WHERE status = 'INVALID'
                AND created_appid IS NOT NULL
                AND object_type IN ('PACKAGE BODY'))
        LOOP
            BEGIN
                l_object_name := k.object_name;
                dbms_output.put_line(chr(10));
                EXECUTE IMMEDIATE k.invalidobject2;
            EXCEPTION 
                WHEN OTHERS THEN
                    dbms_output.put_line('FAILED FOR -->' || l_object_name);
                END;
        END LOOP; 
        inval_cnt1 := inval_cnt1 + 1;
    END LOOP;
EXCEPTION 
WHEN OTHERS    THEN
    dbms_output.put_line('FAILED FOR -->' || l_object_name);
END;
/
select count(*) From user_objects Where status = 'INVALID';

SET ERRORLOGGING OFF
SPOOL OFF

04_End_Upgrade.sql

/* Pre-requisites: Step 3 on Application associated pdb creation is completed */
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application end Upgrade
SPOOL "&SPOOL_PATH"

DECLARE
    l_app_name    VARCHAR2(128);
    l_sql         VARCHAR2(256);
BEGIN
    BEGIN
        SELECT app_name
          INTO l_app_name
          FROM dba_applications 
         WHERE app_implicit <> 'Y'
           AND app_name = (SELECT param_val FROM cstb_param WHERE param_name = 'MULTI_TENANT_APP_NAME');
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Error1 Nodata--->'||SQLERRM);
        WHEN OTHERS THEN
            dbms_output.put_line('Error1 others--->'||SQLERRM);
    END;
    l_sql := 'ALTER PLUGGABLE DATABASE APPLICATION ' || l_app_name||' END UPGRADE ';
    dbms_output.put_line('l_sql: ' || l_sql);
                        
    EXECUTE IMMEDIATE l_sql;
         
EXCEPTION 
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error --->'||SQLERRM);
END;
/

SET ERRORLOGGING OFF
SPOOL OFF

04_Invalids_Recompilation_Outside_Upgrade.sql

/*  Script for Shared Application + Shared Data */
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application Upgrade Invalids Recompilation
SPOOL "&SPOOL_PATH"

DECLARE
    inval_cnt         NUMBER := 0;
    l_object_name     VARCHAR2(240);
BEGIN
    WHILE inval_cnt < 3 LOOP
        --SCRIPT
        FOR J IN (Select 'alter ' || object_type || ' ' || object_name ||' compile' invalidobject1,
                  object_name
                FROM user_objects 
                WHERE status = 'INVALID'
                AND created_appid IS NULL
                AND object_type IN ('VIEW','SYNONYM','PROCEDURE','FUNCTION','PACKAGE','TRIGGER','MATERIALIZED VIEW'))
        LOOP
            BEGIN
                l_object_name := j.object_name;
                dbms_output.put_line(chr(10));
                EXECUTE IMMEDIATE J.invalidobject1;
            EXCEPTION
                WHEN OTHERS THEN
                    dbms_output.put_line('failed for -->' || l_object_name);
            END;
        END LOOP;
        inval_cnt := inval_cnt + 1;
    END LOOP;
EXCEPTION
  WHEN OTHERS THEN
        dbms_output.put_line('FAILED FOR -->' || l_object_name);
END;
/
DECLARE
    inval_cnt1         NUMBER := 0;
    l_object_name     VARCHAR2(240);
BEGIN
    WHILE inval_cnt1 < 3 LOOP
        --SCRIPT
        FOR k IN (Select 'alter package '|| object_name||' compile body' invalidobject2,
                 object_name
                FROM user_objects 
                WHERE status = 'INVALID'
                AND created_appid IS NULL
                AND object_type IN ('PACKAGE BODY'))
        LOOP
            BEGIN
                l_object_name := k.object_name;
                dbms_output.put_line(chr(10));
                EXECUTE IMMEDIATE k.invalidobject2;
            EXCEPTION 
                WHEN OTHERS THEN
                    dbms_output.put_line('FAILED FOR -->' || l_object_name);
                END;
        END LOOP; 
        inval_cnt1 := inval_cnt1 + 1;
    END LOOP;
EXCEPTION 
WHEN OTHERS    THEN
    dbms_output.put_line('FAILED FOR -->' || l_object_name);
END;
/
select count(*) From user_objects Where status = 'INVALID';

SET ERRORLOGGING OFF
SPOOL OFF

05_Start_Upgrade.sql

SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application Upgrade initiation
SPOOL "&SPOOL_PATH"

DECLARE
    l_app_name    VARCHAR2(128);
    l_app_currver VARCHAR2(30);
    l_Sql         VARCHAR2(256);
BEGIN
    
    BEGIN
        SELECT app_name
          INTO l_app_name
          FROM dba_applications 
         WHERE app_implicit <> 'Y'
         AND app_name = (SELECT param_val FROM cstb_param WHERE Param_name = 'MULTI_TENANT_APP_NAME');
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Error1 Nodata--->'||SQLERRM);
        WHEN OTHERS THEN
            dbms_output.put_line('Error1 others--->'||SQLERRM);
    END; 
    
    BEGIN
        SELECT MAX(app_version)
          INTO l_app_currver
          FROM dba_app_versions
         WHERE app_name = l_app_name;
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Error2 Nodata--->'||SQLERRM);
        WHEN OTHERS THEN
            dbms_output.put_line('Error2 others--->'||SQLERRM);
    END;
    
    l_Sql := 'ALTER PLUGGABLE DATABASE APPLICATION ' || l_app_name||' BEGIN UPGRADE '''|| l_app_currver || ''' TO '''|| '&P_APPLICATION_NEXTVER' ||'''';
    dbms_output.put_line('l_sql: ' || l_Sql);                             
    EXECUTE IMMEDIATE l_Sql;
    
    l_Sql := 'ALTER SYSTEM SET DEFAULT_SHARING = NONE';
    dbms_output.put_line('l_sql: ' || l_Sql);
    EXECUTE IMMEDIATE l_Sql;
         
EXCEPTION 
    WHEN OTHERS THEN
        dbms_output.put_line('Error --->'||SQLERRM);
END;
/

SET ERRORLOGGING OFF
SPOOL OFF

06_New_Object_Conversion.sql

/*    Script    for    Shared    Object    Conversion    for    patch-set    */
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Upgrade New object Conversion
SPOOL    "&SPOOL_PATH"

DECLARE
    l_count          NUMBER;
    l_app_deployment VARCHAR2(30);
BEGIN
    SELECT count(*)
	  INTO l_count
	  FROM user_objects
	 WHERE sharing = 'NONE' --to get the new set of DL approot objects if any
	   AND object_name IN
		   (SELECT DISTINCT a.object_name
			  FROM cstm_approot_objects a
			 WHERE sharing = 'DL'
			   AND UPPER(object_type) = 'TABLE'
			   AND EXISTS (SELECT 1
					  FROM user_objects b
					 WHERE b.object_name = a.object_name)
			   AND EXISTS (SELECT 1
					  FROM cstm_approot_functions_menu c
					 WHERE c.function_id = a.function_id
					   AND c.modifiable IN ('Y', 'S')));
    dbms_output.put_line('l_count:    '    ||    l_count);
	
    IF l_count > 0 THEN
        dbms_output.put_line('New DL objects are available');
        SELECT param_val
		  INTO l_app_deployment
		  FROM cstb_param
		 WHERE param_name = 'MULTI_TENANT_DEPLOYMENT_MODEL';
        dbms_output.put_line('l_app_deployment: '||l_app_deployment);
        
        IF l_app_deployment IS NOT NULL AND l_app_deployment = 'SAUA' THEN
            UPDATE smtb_menu menu
               SET menu.approot_flg = 'Y'
             WHERE menu.function_id IN
                   (SELECT function_id
                      FROM cstm_approot_functions_menu
                     WHERE modifiable = 'S'
                    UNION
                    SELECT summary_fn_id
                      FROM cstm_approot_functions_menu
                     WHERE modifiable = 'S'
                       AND summary_fn_id IS NOT NULL) --SMS function id 'S'
               AND menu.approot_flg <> 'Y'; --excluding the already modified approot function ids in menu.
        ELSIF l_app_deployment IS NOT NULL AND l_app_deployment = 'SASDD' THEN
            UPDATE smtb_menu menu
               SET menu.approot_flg = 'Y'
             WHERE menu.function_id IN
                   (SELECT function_id
                      FROM cstm_approot_functions_menu
                    UNION
                    SELECT summary_fn_id
                      FROM cstm_approot_functions_menu
                     WHERE summary_fn_id IS NOT NULL)
               AND menu.approot_flg <> 'Y'; --excluding the already modified approot function ids in menu.
        ELSIF l_app_deployment IS NOT NULL AND l_app_deployment = 'SASDC' THEN
		/*Assumption new table cstm_approot_menu_custom_movedtopdb will be available
            and is populated with the function ids which are moved to PDB as part of custom deployment
            It has 2 columns FUNCTION_ID and SUMMARY_FN_ID*/
      
            UPDATE smtb_menu menu
               SET menu.approot_flg = 'Y'
             WHERE menu.function_id IN
                   (SELECT function_id
                      FROM cstm_approot_functions_menu
                    UNION
                    SELECT summary_fn_id
                      FROM cstm_approot_functions_menu
                     WHERE summary_fn_id IS NOT NULL)
               AND menu.function_id NOT IN --excluding the function ids moved to PDB already.
                   (SELECT function_id
                      FROM cstm_approot_menu_movedtopdb
                    UNION
                    SELECT summary_fn_id
                      FROM cstm_approot_menu_movedtopdb
                     WHERE summary_fn_id IS NOT NULL)
               AND menu.approot_flg <> 'Y'; --excluding the already modified approot function ids in menu.
        END IF;
        
        BEGIN
			FOR I IN (SELECT 'BEGIN ' || chr(10) ||
						   'DBMS_PDB.SET_DATA_LINKED(''&P_APPROOT_USER''' || ',''' ||
						   Object_Name || ''',' || Namespace || '); ' || chr(10) ||
						   'EXCEPTION ' || chr(10) ||
						   'WHEN OTHERS THEN ' || chr(10) ||
						   'DBMS_OUTPUT.PUT_LINE(''ERROR ->''|| SQLERRM); ' ||
						   chr(10) || 'END;' sqlobject
					  FROM user_objects
					 WHERE sharing = 'NONE' --to get the new set of DL approot objects if any
					   AND object_name IN
						   (SELECT DISTINCT a.object_name
							  FROM cstm_approot_objects a
							 WHERE sharing = 'DL'
							   AND UPPER(object_type) = 'TABLE'
							   AND EXISTS (SELECT 1
									  FROM user_objects b
									 WHERE b.object_name = a.object_name)
							   AND EXISTS
							 (SELECT 1
									  FROM cstm_approot_functions_menu c
									 WHERE c.function_id = a.function_id
									   AND c.modifiable IN ('Y', 'S')))) LOOP
				DBMS_OUTPUT.PUT_LINE(chr(10));
				EXECUTE IMMEDIATE I.sqlobject;
				DBMS_OUTPUT.PUT_LINE(I.sqlobject);
			END LOOP;
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.PUT_LINE('Error --->' || SQLERRM);
		END;
    ELSE
        dbms_output.put_line('No new DL objects available');
    END IF;
EXCEPTION    
    WHEN OTHERS THEN
        dbms_output.put_line('Error --->'||SQLERRM);
END;
/

SET ERRORLOGGING OFF
SPOOL OFF

07_Object_Conversion.sql

SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Upgrade object conversion
SPOOL "&SPOOL_PATH"
BEGIN
	FOR I IN (SELECT 'BEGIN ' || chr(10) ||
					   'DBMS_PDB.SET_METADATA_LINKED(''&P_APPROOT_USER''' || ',''' ||
					   Object_Name || ''',' || Namespace || '); ' || chr(10) ||
					   'EXCEPTION ' || chr(10) || 'WHEN OTHERS then ' || chr(10) ||
					   'DBMS_OUTPUT.PUT_LINE(''ERROR ->''|| SQLERRM); ' ||
					   chr(10) || 'END;' sqlobject
				  FROM user_objects
				 WHERE sharing = 'NONE'
                   AND object_type NOT IN ('INDEX', 'LOB', 'TABLE PARTITION','SEQUENCE','JOB','MATERIALIZED VIEW','MATERIALIZED VIEW LOG')
				   AND application = 'Y'
                   AND (object_name,object_type) NOT IN (SELECT object_name,object_type
                                                         FROM   cstm_approot_objects 
                                                         WHERE  function_id  = 'DYNAMIC'    
                                                         AND    sharing      = 'NONE'															 
                                                        )				   
		     ) LOOP
		dbms_output.put_line(chr(10));
		EXECUTE IMMEDIATE I.sqlobject;
		dbms_output.put_line(I.sqlobject);
	END LOOP;
EXCEPTION 
WHEN OTHERS THEN
  dbms_output.put_line('Error --->'||SQLERRM);
END;
/
SET ERRORLOGGING OFF
SPOOL OFF

08_Invalids_Recompilation_Inside_Upgrade.sql

/*  Script for Shared Application + Shared Data */
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application Upgrade Invalids Recompilation
SPOOL "&SPOOL_PATH"

DECLARE
    inval_cnt         NUMBER := 0;
    l_object_name     VARCHAR2(240);
BEGIN
    WHILE inval_cnt < 3 LOOP
        --SCRIPT
        FOR J IN (Select 'alter ' || object_type || ' ' || object_name ||' compile' invalidobject1,
                  object_name
                FROM user_objects 
                WHERE status = 'INVALID'
                AND created_appid IS NOT NULL
                AND object_type IN ('VIEW','SYNONYM','PROCEDURE','FUNCTION','PACKAGE','TRIGGER','MATERIALIZED VIEW'))
        LOOP
            BEGIN
                l_object_name := j.object_name;
                dbms_output.put_line(chr(10));
                EXECUTE IMMEDIATE J.invalidobject1;
            EXCEPTION
                WHEN OTHERS THEN
                    dbms_output.put_line('failed for -->' || l_object_name);
            END;
        END LOOP;
        inval_cnt := inval_cnt + 1;
    END LOOP;
EXCEPTION
  WHEN OTHERS THEN
        dbms_output.put_line('FAILED FOR -->' || l_object_name);
END;
/
DECLARE
    inval_cnt1         NUMBER := 0;
    l_object_name     VARCHAR2(240);
BEGIN
    WHILE inval_cnt1 < 3 LOOP
        --SCRIPT
        FOR k IN (Select 'alter package '|| object_name||' compile body' invalidobject2,
                 object_name
                FROM user_objects 
                WHERE status = 'INVALID'
                AND created_appid IS NOT NULL
                AND object_type IN ('PACKAGE BODY'))
        LOOP
            BEGIN
                l_object_name := k.object_name;
                dbms_output.put_line(chr(10));
                EXECUTE IMMEDIATE k.invalidobject2;
            EXCEPTION 
                WHEN OTHERS THEN
                    dbms_output.put_line('FAILED FOR -->' || l_object_name);
                END;
        END LOOP; 
        inval_cnt1 := inval_cnt1 + 1;
    END LOOP;
EXCEPTION 
WHEN OTHERS    THEN
    dbms_output.put_line('FAILED FOR -->' || l_object_name);
END;
/
select count(*) From user_objects Where status = 'INVALID';

SET ERRORLOGGING OFF
SPOOL OFF

09_End_Upgrade.sql

/* Pre-requisites: Step 3 on Application associated pdb creation is completed */
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application end Upgrade
SPOOL "&SPOOL_PATH"

DECLARE
    l_app_name VARCHAR2(128);
    l_sql VARCHAR2(256);
BEGIN
    BEGIN
        SELECT app_name
          INTO l_app_name
          FROM dba_applications 
         WHERE app_implicit <> 'Y'
           AND app_name = (SELECT param_val FROM cstb_param WHERE param_name = 'MULTI_TENANT_APP_NAME');
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Error1 Nodata--->'||SQLERRM);
        WHEN OTHERS THEN
            dbms_output.put_line('Error1 others--->'||SQLERRM);
    END;
    l_sql := 'ALTER PLUGGABLE DATABASE APPLICATION ' || l_app_name||' END UPGRADE ';
    dbms_output.put_line('l_sql: ' || l_sql);
                        
    EXECUTE IMMEDIATE l_sql;
         
EXCEPTION 
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error --->'||SQLERRM);
END;
/

SET ERRORLOGGING OFF
SPOOL OFF

09_Invalids_Recompilation_Outside_Upgrade.sql

/*  Script for Shared Application + Shared Data */
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application Upgrade Invalids Recompilation
SPOOL "&SPOOL_PATH"

DECLARE
    inval_cnt         NUMBER := 0;
    l_object_name     VARCHAR2(240);
BEGIN
    WHILE inval_cnt < 3 LOOP
        --SCRIPT
        FOR J IN (Select 'alter ' || object_type || ' ' || object_name ||' compile' invalidobject1,
                  object_name
                FROM user_objects 
                WHERE status = 'INVALID'
                AND created_appid IS NULL
                AND object_type IN ('VIEW','SYNONYM','PROCEDURE','FUNCTION','PACKAGE','TRIGGER','MATERIALIZED VIEW'))
        LOOP
            BEGIN
                l_object_name := j.object_name;
                dbms_output.put_line(chr(10));
                EXECUTE IMMEDIATE J.invalidobject1;
            EXCEPTION
                WHEN OTHERS THEN
                    dbms_output.put_line('failed for -->' || l_object_name);
            END;
        END LOOP;
        inval_cnt := inval_cnt + 1;
    END LOOP;
EXCEPTION
  WHEN OTHERS THEN
        dbms_output.put_line('FAILED FOR -->' || l_object_name);
END;
/
DECLARE
    inval_cnt1         NUMBER := 0;
    l_object_name     VARCHAR2(240);
BEGIN
    WHILE inval_cnt1 < 3 LOOP
        --SCRIPT
        FOR k IN (Select 'alter package '|| object_name||' compile body' invalidobject2,
                 object_name
                FROM user_objects 
                WHERE status = 'INVALID'
                AND created_appid IS NULL
                AND object_type IN ('PACKAGE BODY'))
        LOOP
            BEGIN
                l_object_name := k.object_name;
                dbms_output.put_line(chr(10));
                EXECUTE IMMEDIATE k.invalidobject2;
            EXCEPTION 
                WHEN OTHERS THEN
                    dbms_output.put_line('FAILED FOR -->' || l_object_name);
                END;
        END LOOP; 
        inval_cnt1 := inval_cnt1 + 1;
    END LOOP;
EXCEPTION 
WHEN OTHERS    THEN
    dbms_output.put_line('FAILED FOR -->' || l_object_name);
END;
/
select count(*) From user_objects Where status = 'INVALID';

SET ERRORLOGGING OFF
SPOOL OFF

10_PDB_Sync.sql

SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Application PDB Sync
SPOOL "&SPOOL_PATH"

DECLARE
    l_app_name VARCHAR2(128);
    l_sql VARCHAR2(256);
BEGIN
    BEGIN
        SELECT app_name
          INTO l_app_name
          FROM dba_applications 
         WHERE app_implicit <> 'Y';
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Error1 Nodata--->'||SQLERRM);
        WHEN OTHERS THEN
            dbms_output.put_line('Error1 others--->'||SQLERRM);
    END;
    l_sql := 'ALTER PLUGGABLE DATABASE APPLICATION ' || l_app_name||' SYNC ';
    dbms_output.put_line('l_sql: ' || l_sql);                        
    EXECUTE IMMEDIATE l_sql;
         
EXCEPTION 
WHEN OTHERS THEN
  dbms_output.put_line('Error --->'||SQLERRM);
END;
/

SET ERRORLOGGING OFF
SPOOL OFF