Introduction

In today’s article, I will cover an Oracle PLSQL feature that I noticed too often used wrongly or unjustly ignored: PRAGMA SERIALLY_REUSABLE.

What is it?

PRAGMA SERIALLY_REUSABLE is a specialized PL/SQL directive used to optimize memory usage for package state in Oracle Database.

It is especially useful in high-concurrency environments like web applications, where many sessions repeatedly invoke the same package but do not need to retain state between calls.

This article explains the concept in simple terms, shows real examples, and highlights best practices and pitfalls.

The Problem It Solves (Why it exists)

Normally, a PL/SQL package has state that persists for the entire session.

Example:

CREATE OR REPLACE PACKAGE demo_pkg IS
  counter NUMBER := 0;
  PROCEDURE increment;
END demo_pkg;
/

Package body:

CREATE OR REPLACE PACKAGE BODY demo_pkg IS
  PROCEDURE increment IS
  BEGIN
    counter := counter + 1;
    DBMS_OUTPUT.PUT_LINE('Counter = ' || counter);
  END;
END demo_pkg;
/

Typical Behaviour

If a user calls increment 5 times in the same session:

set SERVEROUTPUT on
BEGIN
demo_pkg.increment;
demo_pkg.increment;
demo_pkg.increment;
demo_pkg.increment;
demo_pkg.increment;
END;
Counter = 14
Counter = 15
Counter = 16
Counter = 17
Counter = 18
PL/SQL procedure successfully completed.

The state (counter) remains in memory for the session; as you can see, the value of counter is passed to the next call:

Problem in real systems:

In web applications, we have thousands of sessions therefore:

• The packages are loaded repeatedly
• Memory grows per session state
• Unused package state stays in memory

This can lead to high PGA memory usage an to performance issues

Solution: PRAGMA SERIALLY_REUSABLE

Oracle provides:

PRAGMA SERIALLY_REUSABLE;

When used, this tells Oracle:

“Do NOT keep package state for the entire session. Instead, reuse memory after each call.”

How It Works Internally (Simple View)

With SERIALLY_REUSABLE:

• Package variables exist only during a single call
• After call ends:
  • state is cleared
  • memory is returned to a pool
• Next call starts fresh

Think of it like:

Basic Syntax

Maybe the best place you can apply it to is the package specification:

CREATE OR REPLACE PACKAGE my_pkg IS
  PRAGMA SERIALLY_REUSABLE;
  v_value NUMBER;
  PROCEDURE show_value;
END my_pkg;
/

or Package Body:

 CREATE OR REPLACE PACKAGE BODY my_pkg IS
  PRAGMA SERIALLY_REUSABLE;

  PROCEDURE show_value IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Value = ' || v_value);
  END;
END my_pkg;
/ 

  Important: You MUST declare it in both specification and body!

Example: Demonstrating Behaviour

Without PRAGMA

set SERVEROUTPUT on
BEGIN
  demo_pkg.increment;
  demo_pkg.increment;
END;
  /

Output:

Counter = 1
Counter = 2
PL/SQL procedure successfully completed.

State is preserved.

With PRAGMA SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE sr_pkg IS
  PRAGMA SERIALLY_REUSABLE;
  v_counter NUMBER := 0;
  PROCEDURE inc;
END;
/

CREATE OR REPLACE PACKAGE BODY sr_pkg IS
  PRAGMA SERIALLY_REUSABLE;
  PROCEDURE inc IS
  BEGIN
    v_counter := v_counter + 1;
    DBMS_OUTPUT.PUT_LINE('Counter = ' || v_counter);
  END;
END;
/

Call the following within the same session:

BEGIN
  sr_pkg.inc;
  sr_pkg.inc;
END;
/

Returns:

Counter = 1
Counter = 2
PL/SQL procedure successfully completed.

Which is correct as the second procedure call increases the variable by one

While running the calls in same session in different PLSQL blocks:

BEGIN
  sr_pkg.inc;
END;
/
BEGIN
  sr_pkg.inc;
END;
/

and the output is:

Counter = 1
PL/SQL procedure successfully completed.
Counter = 1
PL/SQL procedure successfully completed.

as the variable value gets wiped when the procedure is called.

Please observe that we call the procedure in total four times with the subtle difference that in one case two calls are together within the same PLSQL block and that drives the difference in output:

Because:

• Each call resets state
• v_counter starts from 0 each time an afterwards, one is added to it!

When to Use It

Best Use Cases

• Web applications (APEX, ORDS, middleware)
• Stateless service calls
• High concurrency systems
• Packages used as utility libraries
• Short-lived computations

Not suitable for:

• Business logic requiring session state
• Caching values across calls
• Complex workflows requiring continuity
• Global session context storage

Key Rules & Restrictions to be aware of

Must be in BOTH spec and body

PRAGMA SERIALLY_REUSABLE;

Package variables are NOT persistent

• Globals are reset after each call
• Not reliable for storing state

Cannot rely on automatic initialisation behaviour

This:

v_x NUMBER := 100;

Will be run/reset every call.

Check the behaviour of the initialisation calls!

Cursor behavior is limited

• Several open cursors in serially reusable packages behave differently!
• Safest: Must be closed before call ends!

Works only with package-level variables

Local procedure variables are unaffected.

Real-World Example: Web Session Utility

Imagine a logging package:

CREATE OR REPLACE PACKAGE log_pkg IS
  PRAGMA SERIALLY_REUSABLE;
  v_user VARCHAR2(100);
  PROCEDURE set_user(p_user VARCHAR2);
  PROCEDURE write_log(p_msg VARCHAR2);
END;
/

Body:

CREATE OR REPLACE PACKAGE BODY log_pkg IS
  PRAGMA SERIALLY_REUSABLE;
  PROCEDURE set_user(p_user VARCHAR2) IS
  BEGIN
    v_user := p_user;
  END;
  PROCEDURE write_log(p_msg VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(v_user || ': ' || p_msg);
  END;
END;
/

Behavior:

Each call must reinitialise:

set SERVEROUTPUT on
BEGIN
  log_pkg.set_user('Alice');
  log_pkg.write_log('Hello');
  log_pkg.set_user('Bob');
  log_pkg.write_log('Hi');
END;
/

Output:

Alice: Hello
Bob: Hi
PL/SQL procedure successfully completed.

But if you forget set_user, value is lost → this is expected behavior.

Performance Benefits

Advantages

1. Reduced PGA memory usage

• No session-long package state

2. Better scalability

• Ideal for thousands of concurrent sessions

3. Faster cleanup

• Memory is reused automatically

Trade-off

• Slight overhead in reinitialization
• Cannot rely on persistent state

Best Practices

Use when stateless design is acceptable

Design packages as:

• function libraries
• transformation utilities
• stateless services

Avoid global variables unless necessary

Instead of:

v_total NUMBER;

Prefer:

FUNCTION calc_total RETURN NUMBER;

Always reset assumptions

Never assume:

• state survives across calls
• variables persist
• initialization runs once

Test in realistic load

Behavior differs significantly under:

• connection pooling
• app servers
• ORDS/APEX

Combine with stateless architecture

Works best with:

• REST APIs
• microservices
• short DB calls

Common Mistakes

Mistake 1: Using as session storage

Trying to store user session data → will fail

Mistake 2: Expecting caching behaviour

Values will NOT persist

Mistake 3: Forgetting dual declaration

Only declaring in spec OR body!

Simple Mental Model

Think of PRAGMA SERIALLY_REUSABLE like: A whiteboard that is erased after every user writes on it.

Without pragma:

• Whiteboard belongs to one person for the whole day

With pragma:

• Whiteboard is cleaned after each use and handed to next person

Conclusion

PRAGMA SERIALLY_REUSABLE is a powerful Oracle feature designed for memory efficiency in stateless environments. It sacrifices session-level state in exchange for scalability and reduced PGA memory consumption.

Use it when:

• You need high concurrency
• You don’t need persistent package state
• You want scalable PL/SQL utility packages

Avoid it when:

• You rely on session-level variables
• You need caching or workflow continuity