Tip#6 Compile Invalid Objects

The Oracle database will invalidate objects if a dependent object is changed. In development environment it is sometimes pain as code is deployed very often and objects like packages, procedures and functions keep changing and need to compile all dependent objects.

Earlier most of the time we use a script to recompile invalid objects like PL/SQL packages and package bodies which may need to run it more than once for dependencies. But then as the number of objects grows so as dependencies and hence the number of times the compile script has to run.

So to avoid executing multiple times the compile script, we created following stored procedure which compiles in hierarchical order to take care of dependencies. Since we had a inter schema dependencies as well and wanted each schema owner to compile there own objects we pass a parameter which is a schema owner who will be compiling their own invalid objects in a schema. As of now inter schema dependencies is not table driven but I will implement it soon.

Please note to have generic solution, it needs to be created in a schema with DBA rights e.g. in Dev I created in SYS and then grant execute access to the schema owners.

CREATE OR REPLACE PROCEDURE Compile_Invalid_Objects
(In_Owner IN VARCHAR2)
IS
CURSOR obj_Cur IS
SELECT 'ALTER '
||DECODE(a.Object_Type,'PACKAGE BODY','PACKAGE',
'TYPE BODY','TYPE',
'SYNONYM',DECODE(a.Owner,'PUBLIC','PUBLIC SYNONYM',
'SYNONYM'),
a.Object_Type)
||' '
||DECODE(a.Owner,'PUBLIC',NULL,
a.Owner
||'.')
||a.Object_Name
||DECODE(a.Object_Type,'JAVA CLASS',' RESOLVE',
' COMPILE')
||DECODE(a.Object_Type,'PACKAGE BODY',' BODY',
'TYPE BODY','BODY') Text
FROM sys.dba_Objects a,
(SELECT MAX(LEVEL) dLevel,
Object_Id
FROM sys.Public_Dependency
START WITH Object_Id IN (SELECT Object_Id
FROM sys.dba_Objects
WHERE Status = 'INVALID'
AND Owner = Upper(In_Owner))
CONNECT BY Object_Id = PRIOR Referenced_Object_Id
GROUP BY Object_Id) b
WHERE a.Object_Id = b.Object_Id (+)
AND a.Status = 'INVALID'
AND a.Owner = Upper(In_Owner)
ORDER BY b.dLevel DESC,
a.Object_Name ASC;
BEGIN
FOR obj_rec IN obj_Cur LOOP
BEGIN
EXECUTE IMMEDIATE obj_rec.Text;
EXCEPTION
WHEN OTHERS THEN
util.Error_Handler('compile_invalid_objects'); -- Log any error
END;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
util.Error_Handler('compile_invalid_objects');
END Compile_Invalid_Objects
/*-----------------------------------------------------------------------------

NAME: compile_invalid_objects
DESC: Compile all invalid objects for the given USER
usage : exec compile_invalid_objects ('SCOTT');
-----------------------------------------------------------------------------*/
;
/

Grant access to SCOTT user,

grant execute on sys.compile_invalid_objects to SCOTT;

I also create a public synonym for the SP,

CREATE PUBLIC SYNONYM COMPILE_INVALID_OBJECTS FOR SYS.COMPILE_INVALID_OBJECTS;