Recompile Invalid Objects

This script recompiles all invalid objects.   Special thanks to the support technicians of Oracle Maestro.

DECLARE SQL_COMPILE VARCHAR2(256); SQL_COMPILE_OBJECT_TYPE VARCHAR2(128); CURSOR C IS SELECT OBJ.OWNER AS OBJECT_OWNER, OBJ.OBJECT_NAME, OBJ.OBJECT_TYPE FROM ALL_OBJECTS OBJ WHERE /* change schema name here */ (OBJ.OWNER = 'Tallarico') AND /* (Change the owner as needed) */ (OBJ.STATUS <> 'VALID') AND (OBJ.OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'VIEW')); BEGIN FOR SOURCE IN C LOOP IF SOURCE.OBJECT_TYPE = 'PACKAGE BODY' THEN /* different syntax for PACKAGE BODY */ SQL_COMPILE_OBJECT_TYPE := 'PACKAGE'; ELSE SQL_COMPILE_OBJECT_TYPE := SOURCE.OBJECT_TYPE; END IF; SQL_COMPILE := 'ALTER ' || SQL_COMPILE_OBJECT_TYPE || ' ' || SOURCE.OBJECT_OWNER || '.' || SOURCE.OBJECT_NAME || ' COMPILE'; EXECUTE IMMEDIATE SQL_COMPILE; END LOOP; END; /