Package variables keep their values for the whole session. That is useful for caches and counters, but sometimes code needs to start clean: in tests, or when a pooled session is handed to a new user. DBMS_SESSION.RESET_PACKAGE throws away the state of every package in the session, and MODIFY_PACKAGE_STATE does the same with more options.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_session.reset_package; dbms_session.modify_package_state(dbms_session.reinitialize); -- or free_all_resources
Both take effect when the current call to the database ends, so check the result in the next block or statement.
Reset All Packages
VISIT_COUNTER holds one variable. A block sets it to 5, RESET_PACKAGE runs, and a new block reads it.
Example:
create or replace package visit_counter is
g_visits pls_integer := 0;
end;
/
begin
visit_counter.g_visits := 5;
end;
/
exec dbms_session.reset_package
begin
dbms_output.enable; -- RESET_PACKAGE reset DBMS_OUTPUT's state too
dbms_output.put_line('visits after reset: ' || visit_counter.g_visits);
end;
/Output:
Package VISIT_COUNTER compiled PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. visits after reset: 0 PL/SQL procedure successfully completed.
The variable is back to its initial value 0. Every package is reset, including DBMS_OUTPUT, which is why the last block calls DBMS_OUTPUT.ENABLE before printing. The example drops the package afterward.
Reinitialize with MODIFY_PACKAGE_STATE
REINITIALIZE resets package state but keeps the memory the packages use, so it is faster when packages are used again soon. FREE_ALL_RESOURCES releases the memory too, like RESET_PACKAGE.
Example:
create or replace package visit_counter is
g_visits pls_integer := 0;
end;
/
begin
visit_counter.g_visits := 5;
dbms_session.modify_package_state(dbms_session.reinitialize);
end;
/
begin
dbms_output.enable;
dbms_output.put_line('visits after reinitialize: ' || visit_counter.g_visits);
end;
/Output:
Package VISIT_COUNTER compiled PL/SQL procedure successfully completed. visits after reinitialize: 0 PL/SQL procedure successfully completed.
The variable is reset to 0 in the next block. The example drops the package afterward.
Things to Know
- The reset applies to all packages in the session; DBMS_SESSION has no call to reset just one.
- Packages declared PRAGMA SERIALLY_REUSABLE need no reset: their state lasts only for one call.
- Open cursors and other resources held by package state are released too.
Related Guides
Conclusion
DBMS_SESSION.RESET_PACKAGE and MODIFY_PACKAGE_STATE reset the state of every package in the session after the current call ends. Use them in tests and connection pools to start clean, remember that DBMS_OUTPUT is reset too, and use SERIALLY_REUSABLE when a package should never keep state.
See my articles more often when you search Google.
