Recompile oracle invalid object from script

Login to oracle using sqlplus command line

prompt>sqlplus login@ora_service

enter password then copy the following text after the ---------- line to sqlplus prompt

---------script start ------------------------
set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool compile_ora_invalid_obj.sql

select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from dba_objects
where status = 'INVALID' and
object_type in ('PACKAGE','FUNCTION','PROCEDURE', 'TRIGGER', 'VIEW');

spool off;

set heading on;
set feedback on;
set echo on;

@compile_ora_invalid_obj.sql
--- script end-------

Note: You may need to run @compile_ora_invalid_obj.sql at sqlplus prompt again in case of some of oracle invalid objects caused by the other.

Posted in: Oracle Tips, Oracle Trick