Document-ID:        15129.1
Subject:            Limits of PL/SQL Program Units
Author:             DWSMITH
Last Modified:      30 Aug 94 


 
                   Limits of PL/SQL Program Units - Windows 
                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
 
1. Triggers and PL/SQL program units. 
 
Under the Windows operating system there is a restriction on the compiled 
program unit size, a function, procedure, package or trigger.  This limit is  
currently (CDE 1) 64k.  This limit should be lifted in the CDE 2 release. 
 
What this limit implies is that when compiled a program unit may not be 
greater than 64k in size.  The difficulty when developing arises because 
there is no correlation between the actual (raw) size of the program unit 
and the compiled size of the program unit.  It has been shown that a program  
unit which is approximately 14.5k in size is just over the 64k limit when  
compiled.   
 
The size of the compiled program unit depends primarily on the number of 
built-ins used within the program unit and will vary between built-ins 
depending on the amount of overloading within the built-in, but  
there are no guidelines as to which are better than others or when the program 
unit is about to exceed the 64k limit. 
 
It is, therefore, advisable to limit the size of program units to 10k  
wherever possible, also encouraging a more modular design.  
 
 
2. Libraries 
 
The same limits apply to program units within libraries as to program units 
within the CDE modules, and the same development strategy should apply. 
 
There is however a different problem when developing libraries.  The libraries 
are attached to modules at design time, and although the actual program units  
are not loaded into memory until they are needed, the .lib file is attached and 
a file handle is held for future use.  Under Windows there is currently a  
limit of 20 file handles per application, this limit is unchanged by the use 
of the FILES DOS variable.  When using Oracle Forms for example, before the  
application is executed, Forms has taken up to 10 file handles already for  
itself.  This clearly depletes the number left for the application. 
 
The only modules this limit effects are the libraries, other CDE modules (fmx, 
mmx files etc.) when executed, are loaded into memory and then closed at the  
file level.  This implies the need for 2 (to be safe) or more 'floating'  
file handles needed to perform this task. 
 
This now leaves the application a maximum of 8 spare handles, which in turn 
only allows the application to attach up to 8 libraries.  As this is a limit 
under Windows, there is little the application developer can do to work 
around this. 
 
If this limit becomes an issue the only solution is to use the referencing 
facilities within the CDE products. 
 
In conclusion when developing under the Windows environment keep program 
units below 10k in size (100 lines of code), and if using libraries keep 
there number down to a working limit of 4 libraries within the application.