Compiling invalid Oracle Object

If you use a lot of packages, stored functions and procedures, they may sometimes become invalid if an object it depends on is changed. We have encountered in a few occasions, our application will hang as a result. To address this, we are using a script (shown below) and schedule it to run once an hour to recompile any invalid objects it encounters.

This is a script is obtained from another blog but was modified so that it will continue to compile the rest even if it encounters error (in case, there are procedures that are in the schema that is still being edited and will not compile correctly, so other invalid object (but compilable) will still get compiled ).

rem -----------------------------------------------------------------------
rem Filename:   cmpall2.sql
rem Purpose:    Compile all invalid database objects
rem             Run this script after each database upgrade or whenever
rem             required.
rem Notes:      If the dependencies between objects are complicated, you can
rem             end up re-compiling it many times, as recompiling some
rem             invalidates others. This script uses dyamic SQL, and
rem             recompile objects based on reverse dependency order.
rem Date:       24-Sep-1998
rem Author:     Fiona Lindsay <f_lindsay@hotmail.com>
rem -----------------------------------------------------------------------

set serveroutput on size 1000000

declare
   sql_statement varchar2(200);
   cursor_id     number;
   ret_val       number;
begin

   dbms_output.put_line(chr(0));
   dbms_output.put_line('Re-compilation of Invalid Objects');
   dbms_output.put_line('---------------------------------');
   dbms_output.put_line(chr(0));

   for invalid in (select object_type, object_name
                   from   user_objects o
                   where  o.status       = 'INVALID'
                     and  o.object_type in ('PACKAGE', 'PACKAGE BODY',
                                            'FUNCTION',
                                            'PROCEDURE', 'TRIGGER',
                                            'VIEW')
                   order  by o.object_type) loop

      begin
       if invalid.object_type = 'PACKAGE BODY' then
          sql_statement := 'alter package '||invalid.object_name||
                           ' compile body';
       else
          sql_statement := 'alter '||invalid.object_type||' '||
                           invalid.object_name||' compile';
       end if;
 
       /* now parse and execute the alter table statement */
       cursor_id := dbms_sql.open_cursor;
       dbms_sql.parse(cursor_id, sql_statement, dbms_sql.native);
       ret_val := dbms_sql.execute(cursor_id);
       dbms_sql.close_cursor(cursor_id);
 
       dbms_output.put_line(rpad(initcap(invalid.object_type)||' '||
                                invalid.object_name, 32)||' : compiled');
      EXCEPTION
          When others then
             dbms_output.put_line('Error in '|| invalid.object_name || ' - Continuing');
      end;
   end loop;

end;
/
EXIT

To use the above script, just copy into a text file and save it as comp_obj.sql. You can then use a batch file to run it as follows:

sqlplus scott/tiger@orcl @comp_obj >> comp_obj.log