Wednesday, February 25, 2009

Compile all invalid packages in a schema : oracle

Came across this from sql developer, Looks like a very useful script, especially when its made available readymade

begin
FOR cur IN
(SELECT OBJECT_NAME, OBJECT_TYPE, owner
FROM all_objects
WHERE object_type in ('PACKAGE','PACKAGE BODY')
and owner = :OBJECT_OWNER AND status = 'INVALID' )
LOOP
BEGIN
if cur.OBJECT_TYPE = 'PACKAGE BODY'
then
EXECUTE IMMEDIATE 'alter package "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile body';
else
EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.owner || '"."' || cur.OBJECT_NAME || '" compile';
end if;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
end loop;
end;

And then another one to check as to what all objects are invalid -

select object_type, count(*)
from user_objects
where status = 'INVALID'
GROUP BY object_type

No comments:

Post a Comment