| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: pls-00123 - Program too Large
Better than my words....the notes themself:
Doc ID: Note:99882.1
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 23-FEB-2000
Last Revision Date: 13-JUN-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
[NOTE:73969.1] PL/SQL Size Limitations
[NOTE:62603.1] 'PLS-123 Program too Large' - Size
Limitations on PLSQL
Packages
And the other:
Doc ID: Note:62603.1
Limitations on PLSQL Packages
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 22-SEP-1998
Last Revision Date: 13-JUN-2000
Overview
This article contains information on PL/SQL package
size limitations.
When limits are reached, you receive the following
error:
PLS-123 Program too large
Size Limitations on PL/SQL Packages
In releases prior to 8.1.3, large programs resulted in
the PLS-123 error.
This occurred because of genuine limits in the
compiler; not as a result
of a bug.
When compiling a PL/SQL unit, the compiler builds a
parse tree. The
maximum size of a PL/SQL unit is determined by the
size of the parse tree.
A maximum number of diana nodes exists in this tree.
Up to 7.3, you could have 2**14 (16K) diana nodes, and
from 8.0 to 8.1.3,
2**15 (32K) diana nodes were allowed. With 8.1.3,
this limit has been
relaxed so that you can now have 2**26 (i.e., 64M)
diana nodes in this tree
for package and type bodies.
Source Code Limits
While there is no easy way to translate the limits in
terms of lines of
source code, it has been our observation that there
have been approximately
5 to 10 nodes per line of source code. Prior to
8.1.3, the compiler could
cleanly compile up to about 3,000 lines of code.
Starting with 8.1.3, the limit was relaxed for package
bodies and type bodies
which can now have approximately up to about 6,000,000
lines of code.
Notes: This new limit applies only to package bodies and type bodies.
Also, you may now start hitting some other compiler limits
before you hit this particular compiler limit.
In terms of source code size, assume that tokens
(identifiers, operators,
functions, etc.), are on average four characters long.
Then, the maximum
would be:
Up to 7.3: 4*(2**14)=64K From 8.0 to 8.1.3: 4*(2**15)=128K With 8.1.3: 4*(2**25)=256M
This is a rough estimate. If your code has many
spaces, long identifiers,
etc., you may end up with source code larger than
this. You may also end
up with source code smaller than this if your sources
use very short
identifiers, etc.
Note that this is per program unit, so package bodies
are most likely to
encounter this limit.
How to Check the Current Size of a Package
To check the size of a package, the closest related
number you can use is
PARSED_SIZE in the data dictionary view
USER_OBJECT_SIZE. This value
provides the size of the DIANA in bytes as stored in
the SYS.IDL_xxx$ tables
and is NOT the size in the shared pool.
The size of the DIANA portion of PL/SQL code (used
during compilation) is
MUCH bigger in the shared pool than it is in the
system table.
For example, you may begin experiencing problems with
a 64K limit when the
PARSED_SIZE in USER_OBJECT_SIZE is no more than 50K.
For a package, the parsed size or size of the DIANA
makes sense only
for the whole object, not separately for the
specification and body.
If you select parsed_size for a package, you receive
separate source and
code sizes for the specification and body, but only a
meaningful parsed size
for the whole object which is output on the line for
the package
specification. A 0 is output for the parsed_size on
the line for the package
body.
The following example demonstrates this behaviour:
CREATE OR REPLACE PACKAGE example AS
PROCEDURE dummy1;
END example;
/
CREATE OR REPLACE PACKAGE BODY example AS
PROCEDURE dummy1 IS
BEGIN
NULL;
END;
END;
/
SQL> start t1.sql;
Package created.
Package body created.
SQL> select parsed_size from user_object_size where name='EXAMPLE';
PARSED_SIZE
185
0
SQL> select * from user_object_size where name='EXAMPLE';
NAME TYPESOURCE_SIZE PARSED_SIZE CODE_SIZE
------------------------------ ----------------------- ----------- ----------
EXAMPLE PACKAGE
51 185 62
0
EXAMPLE PACKAGE BODY
70 0 80
0
Oracle stores both DIANA and MCODE in the database.
MCODE is the actual code
that runs, while DIANA for a particular library unit X
contains information
that is needed to compile procedures using library
unit X.
The following are several notes:
--> Therefore, large procedures and functions should
always be defined within
packages!
References
[BUG:225902] ALLOW BIGGER PLSQL BLOCKS TO BE
COMPILED THAN CURRENTLY
SUPPORTED
[NOTE:73969.1] PL/SQL Size Limitations
[NOTE:99882.1] PL/SQL Size Limits Explained
.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: ctrassens_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Jul 16 2001 - 06:20:36 CDT
 |
 |