Re: Resetting state of PL/SQL package

From: Lloyd Sheen <sqlguy_at_hotmail.com>
Date: Sat, 18 Jan 2003 18:55:07 GMT
Message-ID: <fqhW9.99634$pDv.51171@news04.bloor.is.net.cable.rogers.com>

The package used to reset any other package is DBMS_SESSION and the routine is RESET_PACKAGE.
From the docs (9i). The important word is session, you cannot reset packages in other sessions.
This procedure deinstantiates all packages in this session: It frees all package state.

Memory used for caching execution state is associated with all PL/SQL functions,

procedures, and packages that have been run in a session.

For packages, this collection of memory holds the current values of package

variables and controls the cache of cursors opened by the respective PL/SQL

programs. A call to RESET_PACKAGE frees the memory associated with each of the

previously run PL/SQL programs from the session, and, consequently, clears the

current values of any package globals and closes any cached cursors.

RESET_PACKAGE can also be used to reliably restart a failed program in a session. If

a program containing package variables fails, then it is hard to determine which

variables need to be reinitialized. RESET_PACKAGE guarantees that all package

variables are reset to their initial values.

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E299127.6D54AEBB_at_exesolutions.com...
> Dmitry Rusakov wrote:
>
> > Can I correctly reset pl/sql package state in other sessions from
current
> > session for support caching mechanisms?
> >
> > Sincerely
> >
> > D.Rusakov
>
> If I understand your question ... no. Each session instantiates its own.
>
> But then again I may not understand your question as I have never heard
> anyone refer to resetting a package before.
>
> Daniel Morgan
>
Received on Sat Jan 18 2003 - 12:55:07 CST