PLS-00123:程序太大【转】

PLS-00123:程序太大，是否是ORACLE潜伏多年的小BUG呢？

来源：http://zhang41082.itpub.net/post/7167/484069

 

     一哥们给我提了个问题，说一个PACKAGE，通过PL/SQL DEVELOPER进行编写和调试的，大概超过5000行左右，编译的时候会报错，报错的信息是“PLS-00123:程序太大”，才5000行就太大，ORACLE不至于这么弱吧？首先想到的就是PL/SQL DEVELOPER上有什么限制，把菜单栏=》TOOLS=>PERFERENCES中所有选项挖地三尺，没地方限制啊。GOOGLE了一下，解决的办法很简单：在TOOLS=>PERFERENCES=>ORACLE=>DEBUGGER=>ADD DEBUG INFORMAION WHEN COMPILING，在这个地方把这个勾取消，然后就可以编译通过了。那么下面来仔细看看这个东东是干啥的呢？

        在PL/SQL DEVELOPER的官方文档中这么描述的：
Add debug information when compiling.
When this option is enabled, each compilation in PL/SQL Developer will automatically add debug information, so that variable values can always be viewed and set during a debug session. If you disable this option, you can manually add debug information from the Browser.
        就是说，编译的时候是否添加调试信息进去，那调试信息又是干啥用的呢？

在ORACLE的官方文档（Application Developer's Guide - Fundamentals 10g Release 2 (10.2) B14251-01）有这么一段：
        To be able to either display and change private variables or breakpoint and execute code lines step by step, the debugging connection must be granted DEBUG privilege on the relevant code
In addition to these privilege requirements, the ability to stop on individual code lines and debugger access to variables are allowed only in code compiled with debug information generated. The PLSQL_DEBUG parameter and the DEBUG keyword on commands such as ALTER PACKAGE can be used to control whether the PL/SQL compiler includes debug information in its results. If it does not, variables will not be accessible, and neither stepping nor breakpoints will stop on code lines. The PL/SQL compiler will never generate debug information for code that has been obfuscated using the PL/SQL wrap utility.
        也就是说，这些DEBUG信息可以使得你调试的时候查看变量的值、步进调试、设置断点，如果你编译的时候不加这些选项，那么这些常用的调试功能是没办法使用的，而且这点在那哥们实验中也得到证实，编译是通过了，可是没办法调试，这不是要命啊！

那既然是太长了，限制是多少呢？为什么会有这样的问题呢？后来还是在ORACLE的文档（还是上面的文档）中，找到这么一句：
        PL/SQL Object Size Limitation
The size limitation for PL/SQL stored database objects such as procedures, functions,triggers, and packages is the size of the DIANA (Descriptive Intermediate Attributed Notation for Ada) code in the shared pool in bytes. The UNIX limit on the size of the flattened DIANA/pcode size is 64K but the limit may be 32K on desktop platforms.The most closely related number that a user can access is the PARSED_SIZE in thedata dictionary view USER_OBJECT_SIZE. That gives the size of the DIANA in bytes as stored in the SYS.IDL_xxx$ tables. This is not the size in the shared pool. The size of the DIANA part of PL/SQL code (used during compilation) is significantly larger in the shared pool than it is in the system table.

       这玩意看着也费劲，就看见了64K和32K俩数字，那5000行不至于啊。很多人都是说，因为添加了DEBUG信息后，整个程序的SIZE变大，超过了ORACLE的限制，所以有问题，那也不至于添加这么多的调试信息进去啊。

后来在PL/SQL DEVELOPER的官方网站上找到了下面的链接，说是ORACLE的一个BUG：
http://www.allroundautomations.com/threads/ubbthreads.php?ubb=showflat&Number=15426&Searchpage=1&Main=4213&Words=PLS-00123&Search=true#Post15426

        尝试在SQLPLUS中手工编译，不带DEBUG参数编译通过，带DEBUG参数，编译的时候报Warning: Package altered with compilation errors，真不知道ORACLE的那些内部的PACKAGE都是怎么编译调试的呢？

在METALINK上确实找到了这个BUG，709811，在98年的8.0.5版本中汇报的BUG，FIX的版本居然是11.1.0.2，这BUG居然跨越这么多版本，潜伏这么多年，也真不容易啊。

BTW：那哥们的数据库是10G的

评论

1 楼 fengandfan 2012-11-10  

刚好遇到，受教了