compiling invalid oracle procedures

Question

I have a wrapper procedure(proc_main) that calls some procedures within.

create or replace Procedure proc_main
as
begin

proc_child1;

proc_child2;

proc_child3;

proc_compile_invalids; -- This invokes "alter procedure <procedure_name> compile" statement for all the invalids.

end;
/

proc_child procedures apply some processing logic that involves some steps to rename the tables within.

This invalidates the procedures which is the reason why I have the proc_compile_invalids procedure to set them to a valid state again.

My problem is: when I execute the proc_main procedure, it invalidates the main procedure along with the inner child ones. Hence, When the proc_compile_invalids is called as a last step, it hangs as it is trying to recompile the main calling procedure.

Obviously, it is not an issue if i remove the last step and execute it separately.

I know I could separate them out as 2 different calls by commenting the compile proc and executing it as a stand alone. And i also am aware it is a cosmetic step as oracle would try to compile a procedure before executing the next time. So, the invalids become valid anyway. But, at the end of the execution for that day, they all are in an invalid state and I get questioned by the powers be if it can be avoided !

So, just wanted to know if I can avoid separating the calls and still retain it as a last step in the main procedure.

Any thoughts/pointers much appreciated.

Answer 1

You can use dynamic SQL to break the dependency:

CREATE OR REPLACE PROCEDURE proc_main AS
BEGIN

   EXECUTE IMMEDIATE 'BEGIN proc_child1; END;';

   EXECUTE IMMEDIATE 'BEGIN proc_child2; END;';

   EXECUTE IMMEDIATE 'BEGIN proc_child3; END;';

   proc_compile_invalids;  -- This invokes 
                           -- "alter procedure <procedure_name> compile" 
                           -- statement for all the invalids.

END;

Answer 2

Oracle 11g onward

You can use compile_schema procedure of dbms_utility package instead of proc_compile_ivalids in your main procedure to recompile all invalid procedures, functions, packages, and triggers in the specified schema

create or replace Procedure proc_main
as
begin
  Proc_child1;
  proc_child2;
  proc_child3;
  dbms_utility.compile_schema(schema, false);
end;