Here is a script to recompile invalid objects.
The command to recompile is
alter object_type object_name compile;
The only exception is for the package body. The command to recompile the package body is
alter package package_name compile body;
To take that exception into account, here is the script that will general the "alter" for each invalid database object in the current schema.
select 'alter ' || case when object_type = 'PACKAGE BODY'
then 'PACKAGE'
else object_type
end
|| ' ' || object_name || ' compile ' ||
case when object_type = 'PACKAGE BODY'
then 'body'
else null
end || ';' as mysql
from USER_OBJECTS
where status = 'INVALID';
Hope, it will save you time.
Thanks
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If you are interested in the book "Oracle SQL Scipting". here is the link.
