Oracle Packages 2

[BJCooperIT]

I was researching packages and found:

Oracle packages allow you to group together related items, types and subprograms as a PL/SQL module.

When a procedure in a package is called the entire package is loaded, though it happens to be expensive first time, the response is faster for subsequent calls.

Package allows us to create types, variable and subprograms that are private or public.

Avoid program dependency bottlenecks in your code base. If you create packages that are referenced widely in your application, do everything you can to avoid the need to recompile those programs.

My client has a large package that contains over 5000 lines of code and 19 procedures all relating to updating various bits of data in the database pertaining to a person. None of these procedures rely on each other. There are no variables in the package specification. Until this project, the code has been stable and not recompiled often. This package has become the scary "black box" code that no one person understands. Many of the procedures are only called by batch COBOL programs so there is no long-term caching advantage. The project manager is wondering if we should consider breaking this package up since I am modifying about half of the code.

Are there other scenarios in which logical code groups should not reside together in a package?

Does a package ever become too large?

Thanks!

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

Barb,

The disadvantages of placing objects in a single package are:

1) Space consumption in the SHARED_POOL. If you have a "gigantic" package, then when it runs, Oracle must find that much contiguous space in the SHARED_POOL. Not finding the contiguous space in the SHARED_POOL will throw a run-time Oracle error: "unable to allocate % bytes SHARED_POOL".

2) If your packaged procedures are unrelated to one another and the calling environment rarely uses more than one procedure from the package, then all of the procedures that come in coincidentally with the procedure you are actually using then represent wasted consumption in the SHARED_POOL.

3) The biggest downside of "dumping" everthing into a single package is, IMHO, as you mentioned earlier, Fear Factor: no one wants to maintain something so large, that seems so complex...They worry that changing one piece of the package can negatively impact some other (otherwise unrelated) piece.

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[BJCooperIT]

Thanks Dave, that is the kind of answer I was looking for. There has to be some additional advantage to breaking it up since the calls in the COBOL programs would have to be modified.

Sigh, now if I could just get them to change their naming conventions...

 

[carp]

If there are some procedures that are just used by the COBOL routines, and the rest of the procedures are not used by the COBOL routines, then that might be a good starting point for deciding how to break up the package. That way, you would have two smaller packages, which would be better as explained by SM. Also, only the procedures that are likely to be needed are being loaded when the package is invoked. If you can categorize groups of procedures by the ones that are used together (and, possibly, a small subset of routines that are used in multiple scenarios), then you can subdivide into even smaller packages.
It's a lot like fishing gear - I could build a giant tackle box and whenever I go fishing, I'd just hitch my tackle box to the back of the truck, and off I'd go. No matter what kind of fishing I was going to do, I'd have the right gear with me. But it's easier to put all of my fly-fishing gear in one small box, spinning gear in another, and ice fishing gear in yet a third. Then, the items I always use (knife, forceps, light, etc) go into my vest. So when I'm going fishing, I grab my vest, rod, and the appropriate tackle box - and it all fits quite nicely into my PT Cruiser!

 

[BJCooperIT]

Very good point Carp.

Leaving on vacation soon?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[carp]

No - we're up to are hocks here with a major conversion. But it's Spring here in Colorado and somewhere, there are trout laughing at me for being in a cubicle. But they'll quit laughing in about two weeks!

 

[Thargy]

BJ,

another reason for splitting vast packages is that there are limitations as to what the compiler can cope with. We recently had to split a 'big un' because it exceeded a compiler limit.

As you are obviously already aware, although packages are fantastic, like everything in this world, they have their limits.

Regards

Tharg

Grinding away at things Oracular