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).
