PL/SQL User's Guide and Reference
Release 8.1.6

Part Number A77069-01

Library

Product

Contents

Index

Go to previous page Go to next page

E
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.

Dealing with Size Limits

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 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.

Also, the PL/SQL compiler imposes various limits, some of which are given in Table E-1. Ordinarily, however, the DIANA size limit is exceeded before any of those limits.

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 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 

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.


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index