PL/SQL Program Limits
PL/SQL is designed primarily for high-speed transaction
processing. That design imposes some program limits, which are discussed
in this appendix.
PL/SQL is based on the programming language Ada. As a
result, PL/SQL uses a variant of Descriptive Intermediate Attributed
Notation for Ada (DIANA), which is a tree-structured intermediate
language. It is defined using a meta-notation called Interface
Definition Language (IDL). DIANA provides for communication internal to
compilers and other tools.
At compile time, PL/SQL source code is translated into
machine-readable m-code. Both the DIANA and m-code for a procedure or
package are stored in the database. At run time, they are loaded into
the shared memory pool. The DIANA is used to compile dependent
procedures; the m-code is simply executed.
In the shared memory pool, a package spec, object type
spec, standalone subprogram, or anonymous block is limited to 2**26
DIANA nodes (which correspond to tokens such as identifiers, keywords,
operators, and so on). This allows for ~6,000,000 lines of code unless
you exceed limits imposed by the PL/SQL compiler, some of which are
given in Table E-1.
Table E-1 PL/SQL Compiler Limits
To estimate how much memory a program unit requires, you can query the data dictionary view
user_object_size
. The column parsed_size
returns the size (in bytes) of the "flattened" DIANA. In the following
example, you get the parsed size of a package (displayed on the package
spec line):CREATE PACKAGE pkg1 AS PROCEDURE proc1; END pkg1; / CREATE PACKAGE BODY pkg1 AS PROCEDURE proc1 IS BEGIN NULL; END; END pkg1; / SQL> SELECT * FROM user_object_size WHERE name = 'PKG1'; NAME TYPE SOURCE_SIZE PARSED_SIZE CODE_SIZE ERROR_SIZE -------------------------------------------------------------------- PKG1 PACKAGE 46 165 119 0 PKG1 PACKAGE BODY 82 0 139 0
Unfortunately, you cannot estimate the number of DIANA
nodes from the parsed size. Two program units with the same parsed size
might require 1500 and 2000 DIANA nodes, respectively (because, for
example, the second unit contains more complex SQL statements).
When a PL/SQL block, subprogram, package, or object type exceeds a size limit, you get an error such as program too large.
Typically, this problem occurs with packages or anonymous blocks. With a
package, the best solution is to divide it into smaller packages. With
an anonymous block, the best solution is to redefine it as a group of
subprograms, which can be stored in the database.
No comments:
Post a Comment