-- compile.sql by Chris Kirlew
set echo off

-- ---------------------------------------------
-- compile.sql
-- version 1.0
-- Written by Chris Kirlew
-- Created Dec. 7 2005
-- ---------------------------------------------
prompt set echo on
prompt
set echo on

rem ************************************************
rem ** file:     compile.sql
rem ** purpose:  To compile all invalid objects for a given schema
rem ** Usage:    Edit the file and replace OWNER with the object owner.
rem **
rem **	         SQL>@compile
rem **
rem **           The results of the compile.sql script will be sent to the
rem **           compile.tmp file. Take a look at the compile.tmp file and
rem **           verify that it worked. Then run the compile.tmp file.
rem **
rem **           SQL>!vi compile.tmp
rem **           SQL>@compile.tmp
rem **
rem ** Note:     The script does not do it in order of dependency so it will
rem **           have to be run again if there are any remaining invalid
rem **           objects.
rem **
rem *************************************************

set heading off
set pagesize 0
set linesize 79
set verify off
set echo off

spool compile.tmp

select
  ' '||decode(object_type, 'PACKAGE BODY',
 'alter package' ||' '||owner||'.'||object_name||' compile body;',
 'JAVA CLASS','alter JAVA CLASS '||owner||'.'||'"'||object_name||'"'||' compile;',
 'alter ' ||OBJECT_TYPE||' '||owner||'.'||object_name||' compile;')
 from all_objects
 where owner = 'OWNER'  --use name of objects owner.
 and status = 'INVALID';

spool off;