PL SQL编辑器对代码长度的限制(转)

long存储空间为2M,但实际在pl/sql中只有32K可用,因为PL/SQL编辑器对代码有限制,其对long类型最大为32K-7

可以用以下语句去看包的长度:
select * from user_object_size where name = 'PKG_S1_M5_PERSON_INFO'

oracle原文解释:

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
Dealing with Size Limits
PL/SQL Program Limits E-3
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
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
Table E–1 PL/SQL Compiler Limits
Item Limit
Dealing with Size Limits
E-4 PL/SQL User’s Guide and Reference
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.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9650775/viewspace-923252/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9650775/viewspace-923252/