The following support note covers this topic well...

Article-ID: <Note:62603.1>
Folder: PLSQL
Topic: General Information Articles
Title: 'PLS-123 Program too Large' - Size Limitations on PLSQL
Packages
Document-Type: BULLETIN
Impact: MEDIUM
Skill-Level: NOVICE
Server-Version: 07 to 08
Updated-Date: 13-JUN-2000 17:41:01
References:


Overview
--------

This article contains information on PL/SQL package size limitations. When limits are reached, you receive the following error:

PLS-123 Program too large


Size Limitations on PL/SQL Packages
-----------------------------------

In releases prior to 8.1.3, large programs resulted in the PLS-123 error. This occurred because of genuine limits in the compiler; not as a result of a bug.

When compiling a PL/SQL unit, the compiler builds a parse tree. The maximum size 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.


Source Code Limits
------------------

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.

Notes: This new limit applies only to package bodies and type bodies. Also, you may now start hitting some other compiler limits before you hit this particular compiler limit.

In terms of source code size, assume that tokens (identifiers, operators, functions, etc.), are on average four characters long. Then, the maximum would be:

Up to 7.3: 4*(2**14)=64K
From 8.0 to 8.1.3: 4*(2**15)=128K
With 8.1.3: 4*(2**25)=256M

This is a rough estimate. If your code has many spaces, long identifiers, etc., you may end up with source code larger than this. You may also end up with source code smaller than this if your sources use very short identifiers, etc.

Note that this is per program unit, so package bodies are most likely to encounter this limit.


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.

The size of the DIANA portion of PL/SQL code (used during compilation) is MUCH bigger in the shared pool than it is in the system table.

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

For a package, the parsed size or size of the DIANA makes sense only for the whole object, not separately for the specification and body.

If you select parsed_size for a package, you receive separate source and code sizes for the specification and body, but only a meaningful parsed size for the whole object which is output on the line for the package specification. A 0 is output for the parsed_size on the line for the package body.

The following example demonstrates this behaviour:

CREATE OR REPLACE PACKAGE example AS
PROCEDURE dummy1;
END example;
/
CREATE OR REPLACE PACKAGE BODY example AS
PROCEDURE dummy1 IS
BEGIN
NULL;
END;
END;
/

SQL> start t1.sql;

Package created.


Package body created.

SQL> select parsed_size from user_object_size where name='EXAMPLE';


PARSED_SIZE
-----------
185
0


SQL> select * from user_object_size where name='EXAMPLE';

.....

Oracle stores both DIANA and MCODE in the database. MCODE is the actual code that runs, while DIANA for a particular library unit X contains information that is needed to compile procedures using library unit X.

The following are several notes:

a) DIANA is represented in IDL. The linear version of IDL is stored on disk. The actual parse tree is built up and stored in the shared pool. This is why the size of DIANA in the shared pool is typically larger than on disk.

b) DIANA for called procedures is required in the shared pool only when you create procedures. In production systems, there is no need for DIANA in the shared pool (but only for the MCODE).

c) Starting with release 7.2, the DIANA for package bodies is thrown away, not used, and not stored in the database. This is why the PARSED_SIZE (i.e. size of DIANA) of PACKAGE BODIES is 0.

--> Therefore, large procedures and functions should always be defined within packages!