Recompile all Oracle functions and procedures

Often we need to recompile all Oracle functions and procedures in a schema, because they could be in an invalid state. This could happen after an SQL import or after some changes to stored procedures or functions. Here there are a couple of scripts to help you to compile all invalid functions and procedures.

This first script compiles immediately all functions and procedures (replace MYSCHEMA with your schema name):

BEGIN

FOR R_ROW IN (select * from all_objects where owner = 'MYSCHEMA' and object_type = 'FUNCTION') LOOP
    EXECUTE IMMEDIATE 'ALTER FUNCTION ' || R_ROW.OBJECT_NAME || ' COMPILE ';
null;
END LOOP;
FOR R_ROW IN (select * from all_objects where owner = 'MYSCHEMA' and object_type = 'PROCEDURE') LOOP
    EXECUTE IMMEDIATE 'ALTER PROCEDURE ' || R_ROW.OBJECT_NAME || ' COMPILE ';
null;
END LOOP;

END;
/

This second script just prints to DBMS output the SQL script you need to compile your functions and procedures, i.e. in order to put this script in another SQL script, for example at the end of "create or replace procedure" scripts:

BEGIN

FOR R_ROW IN (select * from all_objects where owner = 'MYSCHEMA' and object_type = 'FUNCTION') LOOP
DBMS_OUTPUT.PUT_LINE ('ALTER FUNCTION ' || R_ROW.OBJECT_NAME || ' COMPILE; ');
END LOOP;
FOR R_ROW IN (select * from all_objects where owner = 'MYSCHEMA' and object_type = 'PROCEDURE') LOOP
DBMS_OUTPUT.PUT_LINE ('ALTER PROCEDURE ' || R_ROW.OBJECT_NAME || ' COMPILE; ');
END LOOP;

END;
/