PL/SQL is based on the Ada programming language. PL/SQL uses a variant of the Intermediate Descriptive Assigned Notation for Ada (DIANA), a tree-structured intermediate language. It is defined using a metanotation called Interface Definition Language (IDL). DIANA is used internally by compilers and other tools.
At the time of compilation, the source text PL/SQL is converted into system code. The DIANA and the system code for a subprogram or package are stored in the database. At runtime, they are loaded into the shared memory set. DIANA is used to compile dependent subprograms; the system code simply runs.
In the shared memory set, a package specification, ADT specification, independent subprogram or anonymous block is limited to 67108864 (2 ** 26) DIANA nodes corresponding to tokens like transponders, keywords, operators and so on. This allows ~ 6,000,000 lines of code, unless you exceed the limits imposed by the PL/SQL compiler, some of which are provided in Table C-1 .
In short, how it works:
1) At the time of compilation, the PL / SQL source code is converted into system code and generates the corresponding DIANA.
2) The DIANA and the system code for a subprogram or package are stored in the database.
3) At runtime, they are loaded into the shared memory set.
4) DIANA is used to compile dependent subprograms; more specifically, to verify / validate whether the subprogram is still valid. this is necessary because as we know that a subprogram can use database objects such as Tables, Views, Synonyms or other stored procs. It may be possible that objects have been changed / removed / discarded the next time you run the program. For example: someone may have discarded the table, the previous or stored function may have changed.
5) After validation is done using DIANA, the system code is simply executed.
Unfortunately, you cannot estimate the number of DIANA nodes from the analyzed size. Two program units of the same size analyzed may require DIANA 1500 and 2000 nodes respectively, because, for example, the second unit contains more complex SQL statements.
More information about DIANA nodes calculations, read this book "Ada-Europe '93: 12th International Ada-Europe Conference", Ada Sans Frontieres ", Paris, France, 14-18 June 1993. Proceedings"
The following support note addresses 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 about PL / SQL package size limitations. When the limits are reached, you get the following error:
Programa PLS-123
Very large size limitations in PL / SQL packages
In versions prior to 8.1.3, major programs resulted in PLS-Error 123. This occurred due to genuine limits in the compiler; not as a result of a bug.
When compiling a PL / SQL unit, the compiler creates a parse tree. The maximum size of a PL / SQL drive is determined by the size of the parse tree. There is a maximum number of Diana nodes in this tree.
Until 7.3, it was possible to have 2 ** 14 (16K) Dian nodes and 8.0 to 8.1.3, 2 ** 15 (32K) Dian nodes were allowed. With 8.1.3, this limit has been relaxed, so you can now have 2 ** 26 (i.e., 64M) Diana nodes in this tree for type and package bodies.
Limits of the source code
Although there is no easy way to convert the boundaries in terms of source lines, it was our observation that there were approximately 5 to 10 nodes per source line. Before 8.1.3, the compiler could compile cleanly up to about 3,000 lines of code.
As of 8.1.3, the limit has been reduced to package bodies and type bodies that can now have approximately up to 6,000,000 lines of code. As already quoted.
Notes: This new limit applies only to package bodies and type bodies. In addition, you can now start to reach other compiler limits before you reach that specific limit.
In terms of source code size, suppose that the tokens (identifiers, operators, functions etc.) have, on average, four characters. 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 could end up with a larger source code than that. You can also end up with a smaller source code than this, if your sources use very short identifiers, etc.
Note that this is per unit program, so it is more likely that package bodies will find this limit.
How to check the current package size
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 DIANA in bytes, as stored in tables SYS.IDL_xxx$, and is NOT the shared set size.
The size of the DIANA part of the PL / SQL code (used during compilation) is MUCH larger in the shared pool than in the system table.
For example, you may start having problems with a 64K limit when the PARSED_SIZE in USER_OBJECT_SIZE not more than 50K.
For a package, the size or size analyzed (size and size Parsed) of DIANA makes sense only for the whole object, not separately for the specification and the body.
If you select parsed_size for a package, it will receive separate source and code sizes for the specification and the body, but only a significant analyzed size for the entire object that is generated in the row for the package specification. One 0 is issued for the parsed_size on the package body line.
The following example demonstrates this behavior:
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 DIANA and MCODE in the database. MCODE is the actual code that runs, while DIANA for a given X library unit contains information needed to compile procedures using the X library unit.
The following are several notes:
a) DIANA is represented in the IDL. The linear version of the IDL is stored on the disk. The actual parse tree is built and stored in the shared set. That’s why the size of DIANA in the shared pool is usually larger than on the disk.
b) The 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 set (but only in MCODE).
c) As of release 7.2, the DIANA for package bodies is discarded, unused and not stored in the database. That’s why the PARSED_SIZE (size of DIANA) of PACKAGE BODIES is 0.
Therefore, procedures and large functions must always be defined
in the packages!
A package is stored in the DIANA database, as a procedure. A package can be used to break the dependency chain, however, perhaps making it disappear. It is my belief that ALL (real) production codes should be in a package, never in an independent procedure or function.
References
Ask Tom - PLS-123 error
Who is Diana and why she does not allow my bank objects
data are compiled?
Program limits C PL/SQL 12c Realease 1
PL/SQL program limits 10g Realease 2