set linesize 80;
set pagesize 10000;
set long 50;
set echo on;

/* ************************************************ */
/*                                                  */
/* Author            : Rick Kupcunas                */
/*                   : rick@kupcunas.com            */
/* Original          : 2000-Jun-05                  */
/*                                                  */
/* This script will "touch" all invalid procs which */
/*   will make the views valid (if possible)        */
/*                                                  */
/* Note: this may need to be ran several times      */
/*   until all procedures are marked valid.  This   */
/*   is because the recompiling of a procedure will */
/*   make all refrencing procedures invalid... for  */
/*   example procedure 'A' refrences procedure 'B', */
/*   if 'B' is compiled after 'A' then 'A' will     */
/*   show to be invalid (even though 'B' is valid). */
/*   Once this occurs, recompile 'A' only to mark   */
/*   the procedure valid...                         */
/*                                                  */
/* ************************************************ */

col dummy     noprint format a1;

/* ********************************* */
/* 1. Generate drop table commands   */
/* 2. Generate create table commands */
/* ********************************* */

select 'alter procedure '||owner||'.'||object_name||' compile;'
from dba_objects
where status = 'INVALID' 
and object_type = 'PROCEDURE'
ORDER BY 1;

spool touch_invalid_procedures.rslt
/
spool off;
undefine schema;
commit;

@touch_invalid_procedures.rslt