Package variables normally keep their values for the whole session: set a counter in one call, and the next call sees it. That is useful for caches, but for packages behind web applications, where every request may come from a different user on a pooled session, state left from one call is wrong and wastes memory. PRAGMA SERIALLY_REUSABLE makes a package's state last for one call only.
Code for This Guide
The main examples are in the examples/packages 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
create [or replace] package name is pragma serially_reusable; ... end name;
If the package has a body, the pragma must appear in both the specification and the body.
State Reset with Each Call
Example:
create or replace package request_counter is
pragma serially_reusable;
g_count pls_integer := 0;
end request_counter;
/
begin
request_counter.g_count := request_counter.g_count + 1;
dbms_output.put_line('first call: ' || request_counter.g_count);
end;
/
begin
request_counter.g_count := request_counter.g_count + 1;
dbms_output.put_line('second call: ' || request_counter.g_count);
end;
/Output:
Package REQUEST_COUNTER compiled first call: 1 PL/SQL procedure successfully completed. second call: 1 PL/SQL procedure successfully completed.
Each block is a separate call to the database, and each starts with g_count at 0 again, so both print 1. The example drops its packages afterward.
Compared with a Normal Package
Example:
-- without SERIALLY_REUSABLE, package state lasts for the whole session
create or replace package session_counter is
g_count pls_integer := 0;
end session_counter;
/
begin
session_counter.g_count := session_counter.g_count + 1;
dbms_output.put_line('first call: ' || session_counter.g_count);
end;
/
begin
session_counter.g_count := session_counter.g_count + 1;
dbms_output.put_line('second call: ' || session_counter.g_count);
end;
/Output:
Package SESSION_COUNTER compiled first call: 1 PL/SQL procedure successfully completed. second call: 2 PL/SQL procedure successfully completed.
Without the pragma, the second call sees the value set by the first and prints 2.
Things to Know
- The memory for a serially reusable package's state is returned after each call, which helps when many sessions use the package.
- Serially reusable packages cannot be called from SQL, for example in a query's select list or from a trigger.
- For data that must persist between calls, use a table or an application context instead of package state.
Related Guides
Conclusion
PRAGMA SERIALLY_REUSABLE limits a package's state to a single database call, so every call starts fresh and memory is released afterward. Use it for stateless packages used by many sessions, and normal packages when state should survive between calls.
