Re: strategies wanted for developing very large packages

From: "Norman Neil" >>
 I keep hitting Oracle's stored object size limit (128K) for my main driving package. I have been continually subdividing into smaller packages (Oracle's suggestion) but I am
now beyond +15,000 lines of PL/SQL so everytime I subdivide, there is a significant amount of recoding to be done! <<<

Warning, since your application is so large there is the possibility that all of it will not fit into the shared pool at one time. This will mean that Oracle will need to be able to fetch packages into memory as they are called. It is much easier for Oracle to do this successfully if the packages are small.  We have a half dozen or so packages at around the 100,000 byte mark and unless we load and pin these into the shared pool at database startup we will on occassion start to get 'module not found' errors from the applications that use the packages. If a package this large becomes invalid, then to get a successful recompilation has required us to terminate user sessions and flush the shared pool. You want to keep your packages as small as practical.

I recommend that you stop coding, and take a look at your design. Estimate the code necessary to complete each task and set up the identified modules in advance. Then go back and fill in the code. This will save you a lot of the going back and recoding of the procedure calls caused by splitting a module. If a module grows too large and needs to be split, now you should be splitting in a decending manner and it is possible that only routines internal to the split module are affected limiting the number of calls you need to recode.

Also remember to use package specifications without accompanying bodies to declare record types and pl/sql tables that you can then use by reference in your other packages.

I hope these ideas help.

Keep your code pieces as small as practical.

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Thu Sep 10 1998 - 00:00:00 CDT