编译无效的oracle过程 [英] compiling invalid oracle procedures

问题描述

我有一个包装器过程(proc_main)，该过程在其中调用某些过程.

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过程应用一些处理逻辑，其中涉及一些步骤来重命名其中的表.

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

这使过程无效，这就是为什么我具有proc_compile_invalids过程以将它们再次设置为有效状态的原因.

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

我的问题是:当我执行proc_main过程时，它会使主过程以及内部子过程无效. 因此，当proc_compile_invalids作为最后一步被调用时，由于尝试重新编译主调用过程而挂起.

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.

我知道我可以通过注释编译过程并将其作为独立程序执行，将它们分为2个不同的调用. 而且我也知道这是一个很简单的步骤，因为oracle会在下次执行之前尝试编译一个过程.因此，无效者仍然有效. 但是，在当天执行死刑后，他们所有人都处于无效状态，而我的权力受到质疑，即是否可以避免！

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.

推荐答案

您可以使用

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;

这篇关于编译无效的oracle过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！