Re: PL/SQL Package size

In article <D8AnA1.4E0_at_freenet.carleton.ca>, Dave Boswell <bd860_at_FreeNet.Carleton.CA> wrote:
>> In <3ogj5v$m71_at_viper.is.rest.tasc.com> careywd_at_jupiter (William D.
>> Carey) writes:
>>>I am currently working on a project which is doing a lot PL/SQL
>>>procedures stored in packages. These packages run in to thousands
>>>of lines. Has anyone heard of any guidelines or recommendations for
>>>package size? Have you any real world experiences you can share? I
>>>would appreciate any feedback you can give me.
>>>Will Carey
>Was going through the forms 4.5 Beta on-line docs and came across a section
>that said on the PC, as to other platforms I'm not sure, PL/SQL pcode is
>limited to 64K. The way it was worded it is not clear if this was a limit
>of one block of code or a source module with many procedures/functions. It
>also said, in the same section, that this was approx 10K of source. Does
>anyone know or have a clearer set or numbers ?
>Dave Boswell
I think it is 32k per block of PL/SQL. A block in PL/SQL can be defined as follows:
Declare
Begin
...
...
end;
The biggest PL/SQL program I wrote was 926 lines. If you exceed the block limit you would get the following error: ORA-4031 : Out of shared memory - RAM buffer exceeded. Fortunately there is a workaround suggested in "Oracle Performance Tuning" from O'Reilly Associates. The block which exceeds 32k should be broken up into sub blocks as shown as follows:
Declare
Begin

Declare /* Main Block */
Begin

Declare
Begin
... /* Sub Block 1 */
End;

Begin
... /* Sub Block 2*/
End;

End; /* Main Block */
Hope this helps.
Mahesh Vallampati
Dept of Electrical Engineering
Texas A and M Univeristy.

PS: Buy the above mentioned book . It is REALLY useful. Received on Tue May 09 1995 - 00:00:00 CEST