Re: difference between a packaged procedure and a stored procedure/function

A copy of this was sent to Jeff Kish <jeff.kish_at_ait-mmii.com> (if that email address didn't require changing) On Thu, 18 Nov 1999 10:54:11 -0500, you wrote:

>Can someone enlighten me as to the differences (and when you would use
>one and not the other) between a packaged procedure and a stored
>procedure/function?
>
>I know the stored procedure/function is compiled, and I assume the
>stored procedure/function can return a value to the calling routine.
>
>Thanks so much,
>
>trying to educate myself,
>
>Jeff

a packaged procedure/function is much like a standalong procedure/function -- they do the same things.

Major benefit of packaged procedures/functions (referred to simply as packaged procedures from now on)

o breaks the dependency chain. If i have a standalone procedure that is called by 50 other things in the database AND i recompile that procedure (found a bug, add a feature) -- the 50 referencing objects will become invalidated and will have to be recompiled before their next execution (they will be recompiled for you automagically but the overhead of the compile will happen). If I use a package, i have a specification and a body. to fix a bug/add a feature, i update the body NOT the spec. The only thing that gets recompiled in my database is the body -- not the 50 other things that reference it. only if the SPEC changes (and it should not after a period of time -- it stays the same) will the other objects become invalid.

that alone is enough to make you say "no standalone procedures"....

o packages can maintain a state (internal counters and the like). standalone procedures cannot.

o packages make for more modular/encapsulated code. a package body can have internal functions that are not exposed via the spec -- no one but the package body can see these functions -- they are hidden, they are private.

o packages can have 'startup' code to initialize themselves once/session.

there are others but those are the ones that come to me off the top of my head...

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 18 1999 - 10:43:13 CST