Re: size limit for triggers

From: Jurij Modic <jmodic_at_src.si>
Date: 1998/10/23
Message-ID: <36304118.8133036@news.siol.net>#1/1

On Thu, 22 Oct 1998 23:15:36 GMT, beansboy_at_my-dejanews.com wrote:

>Are you sure? I remember a limit (64k) in v7.2, but I had the impression that
>v7.3 allowed very, very large procedures...
>
>If the database is 7.3 below, perhaps the limit is actually in the IDE that is
>being used - SQL*Navigator used to have a 64k limit, even though the database
>doesn't (later versions do not have this limitation). You can load larger
>procedures using sql*plus.
>
>-jay

There is still a size limit for PL/SQL units in 7.3. Here is a quote form PL/SQL User's Guide and Reference (Rel 2.3) manual:

<***begin quote***>
Dealing with Size Limitations

PL/SQL was designed primarily for transaction processing. As a result, the PL/SQL compiler limits the number of tokens a block can generate. Blocks that exceed the limit cause a program too large compilation error. Generally, blocks larger than 64K exceed the token limit. However, much smaller blocks can exceed the limit if they contain many variables or complex SQL statements.
<***end quote***>

In reality this 64K is not an exact limitation number. More exact description of this limit can be found in "Oracle Corporate Support Problem Repository":

<***begin quote***>
2. Soln# 2071725.6 THE PL/SQL COMPILER IMPOSES A LIMIT ON THE SIZE..

Solution ID         : 2071725.6
For Problem         : 1016105.6
Affected Platforms  : Generic: not platform specific
Affected Products   : PL/SQL
Affected Components : PLSQL V02.XX
Affected Oracle Vsn : V07.XX

Summary:
THE PL/SQL COMPILER IMPOSES A LIMIT ON THE SIZE OF A PROGRAM UNIT Solution Description:

---

When compiling PL/SQL, the compiler builds a parse tree. The maximum size of a PL/SQL unit is determined by the size of the parse tree. There is a maximum to the number of nodes in this tree. Prior to rdbms 7.3 this number was 16K nodes (16*1024), and in 7.3 this has been relaxed to 32k (32*1024) nodes. Each node is either a identifier, keyword, operator etc.   

To give you a rough estimate what this implies for the source code size: let's assume that identifiers, operators, functions, etc. are on average 4 characters long. Then the maximum source would be:   

Prior to 7.3: 4*16*1024 = 64K
With 7.3: 4*132*1024 = 128K   

This is a rough estimate. If you have lots of spaces, long identifiers etc. in your code, you might end up with source code larger than this. You may also end up with source code smaller than this if your sources use real short identifiers etc.   

Note that this is per program unit, so package bodies are most likely to encounter this limit.
<***end quote***>

Hope this clrifies things a little.

Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Oct 23 1998 - 00:00:00 CDT