Clear public global variables

Sometimes you have public package global resources like variables and cursors.
Besides compiling the package that also clears those variables you can use:



DBMS_SESSION.RESET_PACKAGE; - Resets package states and releases session package memory

"The MODIFY_PACKAGE_STATE interface, introduced in Oracle9i, provides an equivalent of the RESET_PACKAGE capability. It is an efficient, lighter-weight variant for reinitializing the state of all PL/SQL packages in the session."

DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.FREE_ALL_RESOURCES); - provides functionality identical to the DBMS_SESSION.RESET_PACKAGE

DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE); - reinitializes packages without releasing them from memory.

"(..) DBMS_SESSION.REINITIALIZE should exhibit better performance than the DBMS_SESSION.FREE_ALL_RESOURCES option because:
* Packages are reinitialized without actually being freed and recreated from scratch. Instead the package memory gets reused.
* Any open cursors are closed, semantically speaking. However, the cursor resource is not actually freed. It is simply returned to the PL/SQL cursor cache. The cursor cache is not flushed. Hence, cursors corresponding to frequently accessed static SQL in PL/SQL remains cached in the PL/SQL cursor cache and the application does not incur the overhead of opening, parsing, and closing a new cursor for those statements on subsequent use.
* The session memory for PL/SQL modules without global state (such as types, stored-procedures) will not be freed and recreated."