Maximum field and variable length in PL/SQL

[codehead]

Hello All:

What is the maximum field and variable length in PL/SQL? I have the following code that I suspect may be giving me a problem:

vSqlStatement varchar2(8000) ;
vSelect varchar2(1000) ;
vFrom1 varchar2(2000) ;
vFrom2 varchar2(2000) ;
vFrom3 varchar2(2000) ;
vWhere varchar2(500) ;
vOrderBy varchar2(500) ;

where vSqlStatement is the concatenation of all of the other statements as follows:

vSqlStatement := vSelect || vFrom1 || vFrom2 || vFrom3 || vWhere || vOrderBy;

Thanks!

 

[ThomVF]

VARCHAR2 is limited to 32K in PL/SQL

What is your error? =================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[codehead]

Well, actually, Oracle isn't giving me an error. It compiles o.k., but when I try to access the Oracle function in Actuate, it gives me the generic message that always means there is something wrong with my function. I have checked the straight SQL part of the function and it runs fine by itself, so I believe the problem is with the function portion or the package that it is in.

Anyway, I would like to know what the maximums are, for future reference.

Thanks!

 

[jee]

Here is something copied from the PL/SQL manual (sorry for the poor formatting, but I just copied and pasted):

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