Re: What are the adavantages on Packages?
Date: 1998/01/28
Message-ID: <34d89b78.28761246_at_192.86.155.100>
A copy of this was sent to dave_at_digidem.com (if that email address didn't require changing) On Wed, 28 Jan 1998 12:51:34 -0600, you wrote:
One of the major advantage of pl/sql packages is in the breaking of the dependency chain. An example works best. Lets say you have procedures A, B, C. A calls B, B calls C. Therefore, B is dependent on C, and A on B. You find a bug in procedure C. It only takes a second to fix and you recompile C. What you'll discover is that procedure B got invalidated when you compiled C. The next time B is executed, it will auto compile but it will have to be recompiled none the less. Also, since B went invalid and will be recompiled -- so must A.
Spread that example out over many many inter-dependent procedures in your database and you'll find that a single procedure getting recompiled can invalidate alot of code. Your database could be spending alot of time recompiling code instead of running queries.
Packages break this dependency. Lets say now that procedure A is in package Pa, B in Pb, C in Pc. the packages have 2 parts -- the spec and the body. The spec defines what functions/procedures/data a package has that anyone can call. The body has the implementation and hidden procedures (procedures local to the package, callable by other routines in the package but not from outside the package). In this example, the package body of Pb is dependent on the Package Spefication of Pc (not the body but the spec). Pb is dependent on the definition of C in Pc, not on the implementation -- the only thing Pb cares about is the inputs and outputs of C. Likewise, the package body Pa is dependent on the package spec of Pb, not the body.
Now, you find that bug in procedure C again. You fix the package body Pc and recompile it. Nothing else goes invalid. the specification (or interface) didn't change hence nothing dependent on the spec of Pc went invalid. You can fix that one procedure without recompiling all of the code in your database.
Another major feature of packages is modular coding and encapsulation. It is very common to have some utility functions to perform a process. These functions have no meaning outside of this process and in fact should never be called by anyone directly, only by the process itself. I would put the process in a packge, expose in the specification for the package the interface other developers can use, and in the package body "hide" from everyone all these little functions/procedures I need but they have no need for. Sort of like private member functions in a C++ class.
Another major feature is state. A package body/spec can maintain a state. Instead of maintaining everything in the client, I can maintain lots of information in the database in the package body/spec. I can have real data structures with stuff in them that lives for the life of the connection. This can be pretty powerful and will allow you to move large portions of your logic into the database -- every call isn't a 'one off' thing, pl/sql can remember stuff from call to call (if you use packages)...
I could go on but thats a couple of reasons. When I programmed in Ada, we had the opportunity to write standalone procedures and functions -- but we never did, we always used packages. In C++ classes became more popular then a simple procedure/function (you have the iostream class, not the fread, fwrite, fprintf, etc family of functions).
You should approach pl/sql coding as you would approach developing any software -- group the routines into modules of related functions. Keep the modules a reasonable, maintainable size....
>I have a set of stored procedures that i am calling from a series of OCI
>programs. I need to define a package to hold a global cursor variable in
>order to process the result sets, but should i put these procs into
>packages? What is the advantage to using packages? I understand the
>advantage of stored procedures (in memory, pre-parsed,...) but do
>packages have any additional benefits beyond encapsulating the
>procedures?
>
>I have 3 options that i can take - any comments as to the
>advatages/disadvatages to these:
>
>1. all stored procedures exist as a stand-alone stored procedure 2. group
>my stored procedures into several different packages by functionality 3.
>create 1 huge package that contains all of the stored procedures that i
>am going to call from my OCI program.
>
>From the OCI programmers point of view there is no difference, from the
>PL/SQL programmers point of view there is almost no difference. So then
>what option is best?
>
>Thanks for any help!
>
>-david m rosner
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 28 1998 - 00:00:00 CET