Re: nullifying all variables in a package

If you are going for RESET_PACKAGE, please review the usage notes for it in the docs - it has some limitations. The most important of them is that package state is not reset immediately after the proc is called, only after the PL/SQL call that included a call to this routine is complete. That is, you probably will need to call this procedure from a dynamic anonymous PL/SQL block.
Another issue (and quite bothering to me) is that although package state gets reset for all packages, their initialization block is not re-executed next time they are accessed, so if that init block presets some of the variables, it won't happen after the RESET_PACKAGE call. Contexts are also not reset by this call.
Also note that starting with 8.1.7.2, there is another procedure in DBMS_SESSION - MODIFY_PACKAGE_STATE() - which accepts additional parameter, which can be 1 or 2. 1 does exactly the same as RESET_PACKAGE call, while 2 is a lightweight reset - it does not flush cursor cache and it does not release memory structures associated with packages. Consequently, it executes faster and does not destroy certain structures, like cached cursors, thus saving Oracle from reinstantiating them.

--
Vladimir Zakharychev (bob_at_dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Kamal" <kamal80_at_virgilio.it> wrote in message
news:4e766a02.0301240647.454ae71a_at_posting.google.com...


> The best solution for me it's Vladimir M. Zakharychev's


> dbms_session.reset_package.

> It's just what I needed.

>

> For Matthias Rogel's solution: it's useful, but I didn't want to

> mantain that procedure every time I need a new global variable.

>

> Tnx to all!

>

> Kamal