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

Another key characteristic, one on which those of us with the database "exposed" to the web (even IntrAnets) is that the package body can provide you a means to control the access paths to the underlying raw data.

For example:

We have our web users "get" to the data via an OWA call to a procedure, which in turn, calls the appropriate procedure(s) from within a package, passing the appropriate parameters;

the packaged procedures terminate "appropriately" if the parameters are not valid.

http://.../owa/profile_main

which call procedure profile_main

profile_main call PACKAGED PROCEDURE prmain(userid,ip,choice,...etc)

packaged procedure prmain() does the work and "spits" out html

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:XCs0ODbSSQNrpMf1eFnuREkXRHSM_at_4ax.com...
> 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 - 17:15:00 CST