| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> oracle trivia: maximum size of a stored procedure
This may be of interest to some people. I never knew what the exact maximum
size was for a stored procedure. One of my colleagues asked me why the
manual said that the size limit for a trigger was 32K when he had personally
seen triggers bigger than that. Below you see the answer from Oracle (on the
Metalink PL/SQL forum.)
My question:
In the manual
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920
/a96590/adg13trg.htm#431
Oracle9i Application Developer's Guide - Fundamentals, Release 2 (9.2), Part
Number A96590-01, Chapter 15 Using Triggers
I see this:
... Note: The size of the trigger cannot be more than 32K. ...
The answer, from (someone)@oracle.com
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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 Wed Jun 18 2003 - 14:48:49 CDT
 |
 |