Why does a stored proc become invalid

[kris109]

A stored procedure becomes invalid typically when a referenced object is altered or becomes invalid. Here is a script to find out how procedures are invalid and to recompile them automatically.

set heading off;
set feedback on;
set pages 10000;

prompt ****** NOTE: Append a .sql when entering spoolfile name

spool /temp/&&spoolfile

select 'alter '||object_type, object_name
||' compile;'
from user_objects
where status = 'INVALID'
and object_type != 'PACKAGE BODY'
union
select 'alter package '||NULL, object_name
||' compile body;'
from user_objects
where status = 'INVALID'
and object_type = 'PACKAGE BODY';

spool off

@/temp/&&spoolfile;

host rm /temp/&&spoolfile; del \temp\&&spoolfile;

undefine spoolfile;