Compiling Invalid Objects

[krishnaindia2007]

I am using the following code to compile invalid objects. Some times i need to run this code four to five times. Is there any better code to compile invalid objects at a time? set head off set feedback off set pagesize 0 spool com.sql select 'alter '|| object_type||' '|| object_name ||' compile ; ' from user_objects where status = 'invalid' and object_type != 'package body' order by object_type / select 'alter package '|| object_name ||' compile body ; ' from user_objects where status = 'invalid' and object_type = 'package body' order by object_type / spool off @com.sql set head on set feedback on set pagesize 100 /

[susarlasireesha]

EXEC DBMS_UTILITY.COMPILE_SCHEMA( 'schema-name' );

[susarlasireesha]

set termout on
set serverout on

DECLARE
cursor cur_invalid_objects is
select object_name,
object_type
from user_objects
where status = 'INVALID';

rec_columns cur_invalid_objects%ROWTYPE;
err_status NUMERIC;
BEGIN
dbms_output.enable (10000);

open cur_invalid_objects;

loop
fetch cur_invalid_objects
into rec_columns;

EXIT WHEN cur_invalid_objects%NOTFOUND;
dbms_output.put_line ('Recompiling '
|| rec_columns.object_type
|| ' '
|| rec_columns.object_name);
dbms_ddl.alter_compile (rec_columns.object_type, NULL,
rec_columns.object_name);
end loop;

close cur_invalid_objects;
EXCEPTION
When others then
begin
err_status := SQLCODE;
dbms_output.put_line (' Recompilation failed : '
|| SQLERRM (err_status) );

if (cur_invalid_objects%ISOPEN) then
CLOSE cur_invalid_objects;
end if;
exception
when others then
null;
end;
end;