C PL/SQL Program Limits

This appendix describes the program limits that are imposed by the PL/SQL language. 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), a tree-structured intermediate language. It is defined using a meta-notation called Interface Definition Language (IDL). DIANA is used internally by compilers and other tools.

At compile time, PL/SQL source code is translated into system code. Both the DIANA and system code for a subprogram 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 subprograms; the system code is simply executed.

In the shared memory pool, a package spec, object type spec, standalone subprogram, or anonymous block is limited to 67108864 (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 C-1.

Table C-1 PL/SQL Compiler Limits

Item Limit

bind variables passed to a program unit


exception handlers in a program unit


fields in a record


levels of block nesting


levels of record nesting


levels of subquery nesting


levels of label nesting


levels of nested collections

no predefined limit

magnitude of a PLS_INTEGER or BINARY_INTEGERvalue


number of formal parameters in an explicit cursor, function, or procedure


objects referenced by a program unit


precision of a FLOAT value (binary digits)


precision of a NUMBER value (decimal digits)


precision of a REAL value (binary digits)


size of an identifier (characters)


size of a string literal (bytes)


size of a CHAR value (bytes)


size of a LONG value (bytes)


size of a LONG RAW value (bytes)


size of a RAW value (bytes)


size of a VARCHAR2 value (bytes)


size of an NCHAR value (bytes)


size of an NVARCHAR2 value (bytes)


size of a BFILE value (bytes)

4G * value of DB_BLOCK_SIZE parameter

size of a BLOB value (bytes)

4G * value of DB_BLOCK_SIZE parameter

size of a CLOB value (bytes)

4G * value of DB_BLOCK_SIZE parameter

size of an NCLOB value (bytes)

4G * value of DB_BLOCK_SIZE parameter

To estimate how much memory a program unit requires, you can query the static data dictionary view USER_OBJECT_SIZE. The column PARSED_SIZE returns the size (in bytes) of the "flattened" DIANA. For example:

SQL> SELECT * FROM user_object_size WHERE name = 'PKG1';

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 PLS-00123: 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.

For more information about the limits on data types, see Chapter 3, "PL/SQL Data Types." For limits on collection subscripts, see Referencing Collection Elements.