RE: Limits on PL/SQL block?

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 08 Aug 2003 10:29:36 -0800
Message-ID: <F001.005C9AB6.20030808102936@fatcity.com>

> -----Original Message-----
> From: Rudy Zung [mailto:rzung_at_printcafe.com]
>
> I'm seeing a "PLS-00123 program too large" error. Oracle's
> documentation says that the actual limit on the size of
> the block is dependant on the mix of statements in the
> PL/SQL block. Does anyone know how Oracle determines this
> limit? Is it a pure size of PL/SQL block in bytes, or is
> it number of unique statements in the block, or is it
> dependant on how much redo that the block may generate?

I asked a question about the maximum trigger size on Metalink recently (the 9.2 documentation said that the maximum trigger size is 32K). Short answer - the easiest way to tell if a PL/SQL block is too big: try it and see if you get an error.

Here is the full answer from Oracle:

---

The limit of 32k for a trigger is platform dependent. The 32K limit is set taking into account the limit of 64K DIANA Nodes which includes the m-code and parsed-code. You may therefore end up having larger source code if the parsed-code ends up being lesser than the source code.

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.

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.

You can query the user_object_size table on the database to find out the size of the procedure/package/plsql block.

---

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Fri Aug 08 2003 - 13:29:36 CDT