Subject: PL/SQL Size Limits Explained Creation Date: 23-FEB-2000 Overview -------- This article provides an overview of PL/SQL size limits for the 8.1.x releases. It is intended as a quick reference and is a synopsis of documented Oracle behavior. PL/SQL Size Limits Explained ---------------------------- Oracle's PL/SQL programming language is based upon the ADA programming language. 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. PL/SQL source code for a procedure or a package is stored in the database using the following two constructs: o m-code o DIANA The m-code is the actual executable, while the DIANA provides source code dependency trees. At runtime, the m-code and DIANA are loaded into the shared pool. The m-code is the executable, while the DIANA compiles dependent procedures. According to 8.1.6 documentation the following size limits apply: "In the shared pool, a package spec, object type spec, stand-alone subprogram, or anonymous block is limited to 64K DIANA nodes. The nodes correspond to tokens such as identifiers, keywords, operators, and so on. The m-code is limited to 64K compiler-generated temporary variables." Although the PL/SQL compiler enforces its own size limits (listed below), usually it is the DIANA size limits which are reached first. The following is an excerpt from Appendix E of the "PL/SQL Users Guide and Reference 8.1.6 Release": Table E-1 PL/SQL Compiler Limits (8.1.X release) 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 BIFLE value (bytes) 4G size of a BLOB value (bytes) 4G size of a CLOB value (bytes) 4G size of an NCLOB value (bytes) 4G Related Documents ----------------- "PL/SQL User's Guide and Reference Release 8.1.6", (A77069-01), Appendix E ORACLE 8 FEATURES (LANGUAGES, COMPILERS, NLS, AND PLSQL) [NOTE:73969.1] PL/SQL Size Limitations [NOTE:62603.1] 'PLS-123 Program too Large' - Size Limitations on PLSQL Packages