PLS-00123: program too large (Diana nodes) while trying to compile a package

While compiling a package, I ran into an error message:

Error: PLS-00123: program too large (Diana nodes)
Line: 1

The package in question has about 1k lines (spec) + 13k lines in body. While researching on this, I came across this Ask Tom question

When compiling a PL/SQL unit, the compiler builds a parse tree. The maximum size 开发者_StackOverflow中文版of a PL/SQL unit is determined by the size of the parse tree. A maximum number of diana nodes exists in this tree.

Up to 7.3, you could have 2**14 (16K) diana nodes, and from 8.0 to 8.1.3, 2**15 (32K) diana nodes were allowed. With 8.1.3, this limit has been relaxed so that you can now have 2**26 (i.e., 64M) diana nodes in this tree for package and type bodies.

While there is no easy way to translate the limits in terms of lines of source code, it has been our observation that there have been approximately 5 to 10 nodes per line of source code. Prior to 8.1.3, the compiler could cleanly compile up to about 3,000 lines of code.

Starting with 8.1.3, the limit was relaxed for package bodies and type bodies which can now have approximately up to about 6,000,000 lines of code.

This is a rough estimate. If your code has many spaces, long identifiers, etc., you may end up with source code larger than this.

Now even if you take into consideration the last list about many spaces & large identifiers, I think it's reasonable to conclude that it's no where close the limits referred above.

Further more,

How to Check the Current Size of a package:

To check the size of a package, the closest related number you can use is PARSED_SIZE in the data dictionary view USER_OBJECT_SIZE. This value provides the size of the DIANA in bytes as stored in the SYS.IDL_xxx$ tables and is NOT the size in the shared pool.

[...]

For example, you may begin experiencing problems with a 64K limit when the PARSED_SIZE in USER_OBJECT_SIZE is no more than 50K.

Querying this view gives a result of 48929 - so I assume it's fair to size is 47k ?

The weird part is, fetching the same object from another schema and running it in the area I'm having problem results in successful compilation.

So why is this particular area causing problem ?

---

Does the program you compile your code with add debug information? Apparently it makes difference illustrated on this forum post.

The issue when doing a compile for debug is the extra code that is added in for the debugging.

You can try these queries to see:

ALTER PACKAGE debug COMPILE;
SELECT type, source_size, parsed_size, code_size
FROM user_object_size
WHERE name = 'DEBUG';

ALTER PACKAGE debug COMPILE DEBUG;
SELECT type, source_size, parsed_size, code_size
FROM user_object_size
WHERE name = 'DEBUG';

Observe the differences in the code_size when you compile for debug.

If you're compiling with DEBUG, try to compile in normal so that it does not generate extra code which can generate your error.