PLS-00123:程序太大

记得去年写了一个16000行左右的存储过程包，在pl/sql developer里编译时提示“PLS-00123:程序太大”的错误。只能在sql*plus里编译过去，而且编译后也可以正常使用。

 

当时认为错误提示很明显，程序写得太大了，但这个包里是一个整体的业务逻辑，没办法拆分。所以就这样用了。

 

今年做业务整合的时候，拆分了这个包，但仍出现该错误，此时才发现应该不是程序太大。而上个月我写过一个2000多行的case嵌套过程，也是提示这个错误。于是想到可能是嵌套的层次太多导致的。便一点点地删除代码进行查找，终于找到了产生问题的代码块。

 

我们看oracle对该错误的解释：

PLS-00123 program too large

Cause: PL/SQL was designed primarily for robust transaction processing. One consequence of the special-purpose design is that the PL/SQL compiler imposes a limit on block size. The limit depends on the mix of statements in the PL/SQL block. Blocks that exceed the limit cause this error.

Action: The best solution is to modularize the program by defining subprograms, which can be stored in an Oracle database. Another solution is to break the program into two sub-blocks. Have the first block INSERT any data the second block needs into a temporary database table. Then, have the second block SELECT the data from the table.

最后，我发现有些代码块的嵌套太多，之后将代码块拆分成几个子过程后，错误提示消失。