Invalids_Recompilation_Outside_Upgrade.sql
Purpose
This script is run for invalid script by connecting to the common user in approot outside the upgrade.
Syntax
/* 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