Script to Compile All invalid Objects in a Database Schema

Most of the times we face a situation where you see lots of interdependent components would be in invalid state after compiling a new or existing oracle object. Below given script can be used to compile all invalid objects.

set serveroutput on size 1000000
DECLARE CURSOR invalidcurs_1 IS select * from user_objects where status != 'VALID' and object_type not in ('TRIGGER','VIEW');

CURSOR invalidcurs_2 IS select * from user_objects where status != 'VALID' and object_type in ('TRIGGER','VIEW');

sql_str VARCHAR2(100);
ret_code INTEGER;
m_cur INTEGER;
l_owner varchar2(10);

BEGIN

dbms_output.enable(1000000);
l_owner := '&owner';
FOR rec IN invalidcurs_1
LOOP
dbms_ddl.alter_compile( rec.object_type, l_owner, rec.object_name);
dbms_output.put_line( 'Compiled: ' l_owner '.' rpad(rec.object_name, 30) ' => ' rec.object_type);
END LOOP;

FOR rec IN invalidcurs_2
LOOP
sql_str := 'ALTER ' rec.object_type ' ' rec.object_name ' COMPILE';
m_cur := dbms_sql.open_cursor; dbms_sql.parse (m_cur, sql_str, 2);
ret_code := dbms_sql.execute (m_cur);
dbms_sql.close_cursor (m_cur);
dbms_output.put_line( 'Compiled: ' l_owner '.' rpad(rec.object_name, 30) ' => ' rec.object_type);
END LOOP;
EXCEPTION
WHEN others THEN
dbms_output.put_line ('Error: ' SQLERRM);
END;