Skip Headers

PL/SQL User's Guide and Reference
10g Release 1 (10.1)

Part Number B10807-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

E PL/SQL Program Limits

This appendix discusses 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 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

Item Limit
bind variables passed to a program unit 32K
exception handlers in a program unit 64K
fields in a record 64K
levels of block nesting 255
levels of record nesting 32
levels of subquery nesting 254
levels of label nesting 98
magnitude of a BINARY_INTEGER value 2G
magnitude of a PLS_INTEGER value 2G
objects referenced by a program unit 64K
parameters passed to an explicit cursor 64K
parameters passed to a function or procedure 64K
precision of a FLOAT value (binary digits) 126
precision of a NUMBER value (decimal digits) 38
precision of a REAL value (binary digits) 63
size of an identifier (characters) 30
size of a string literal (bytes) 32K
size of a CHAR value (bytes) 32K
size of a LONG value (bytes) 32K-7
size of a LONG RAW value (bytes) 32K-7
size of a RAW value (bytes) 32K
size of a VARCHAR2 value (bytes) 32K
size of an NCHAR value (bytes) 32K
size of an NVARCHAR2 value (bytes) 32K
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 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 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.