Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 38 ยท Packages and Triggers

Package State, Overloading and Initialization

Understand persistent package state, session scope, overloaded subprograms, the initialization block, SERIALLY_REUSABLE and the ORA-04068 error in Oracle PL/SQL.

Variables declared in a standalone procedure vanish when it ends. Variables declared in a package behave very differently: they live as long as the session does. That is powerful (you get caches, counters and configuration without any table) and dangerous (state can leak between calls in surprising ways). This lesson explains package state, overloading, and the one-time initialization block.

Oracle cannot run on this site. The PL/SQL is read-only and its outputs are shown only where they are certain. The runnable examples are Python stand-ins that model per-session state.

Package state is per session

When a session first uses a package, Oracle loads it and runs its initialization. Any variable declared at package level (in the spec or at the top of the body) then keeps its value between calls in that session. Another session gets its own independent copy.

SQL
CREATE OR REPLACE PACKAGE call_counter AS
  FUNCTION next_value RETURN NUMBER;
  PROCEDURE reset;
END call_counter;
/
CREATE OR REPLACE PACKAGE BODY call_counter AS
  g_count NUMBER := 0;                 -- persists for the session

  FUNCTION next_value RETURN NUMBER IS
  BEGIN
    g_count := g_count + 1;
    RETURN g_count;
  END;

  PROCEDURE reset IS
  BEGIN
    g_count := 0;
  END;
END call_counter;
/

SELECT call_counter.next_value FROM dual;   -- 1
SELECT call_counter.next_value FROM dual;   -- 2 (same session)

Open a second connection and the first call there returns 1 again. The state is private to each session and is not part of any transaction: a ROLLBACK does not restore g_count.

Typical good uses are caches of small lookup data, a debug flag, or a "current user context". Typical bad uses are anything that has to be correct across sessions (a global invoice number, say): use a sequence or a table, because package state is invisible to other sessions.

Python Sessions each get their own state (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The initialization block

A package body can end with an unnamed block between its last subprogram and END. Oracle runs it once per session, the first time anything in the package is referenced:

SQL
CREATE OR REPLACE PACKAGE BODY app_config AS
  g_tax_rate NUMBER;

  FUNCTION tax_rate RETURN NUMBER IS
  BEGIN
    RETURN g_tax_rate;
  END;

BEGIN                                    -- initialization block
  SELECT value_num INTO g_tax_rate
    FROM settings
   WHERE name = 'TAX_RATE';
END app_config;
/

Keep it short and robust. If the block raises an exception, the first call fails with ORA-06512, and the next call tries to initialize again. Avoid slow work here, because every session pays for it.

State and recompiles: ORA-04068

Because the state is held in session memory, recompiling a package destroys it. A session that was using the old body gets this the next time it calls the package:

Text
ORA-04068: existing state of packages has been discarded

The call fails once. The next call re-initializes and succeeds. Connection pools that keep sessions open for days make this visible, so avoid deploying package changes mid-day if code depends on package state, or make the code retry once on ORA-04068. The pragma SERIALLY_REUSABLE makes package state last only for the duration of one server call (one database call from a client) instead of one session, which saves memory in huge session counts but also removes the "remembered across calls" feature.

Overloading

Inside a package (but not for standalone subprograms) you can define several subprograms with the same name and different parameter lists. Oracle picks the right one at compile time from the arguments' types and counts.

SQL
CREATE OR REPLACE PACKAGE formatter AS
  FUNCTION to_text (p_value IN NUMBER)   RETURN VARCHAR2;
  FUNCTION to_text (p_value IN DATE)     RETURN VARCHAR2;
  FUNCTION to_text (p_value IN BOOLEAN)  RETURN VARCHAR2;
END formatter;
/
SQL
BEGIN
  DBMS_OUTPUT.PUT_LINE(formatter.to_text(42));
  DBMS_OUTPUT.PUT_LINE(formatter.to_text(DATE '2026-11-12'));
  DBMS_OUTPUT.PUT_LINE(formatter.to_text(TRUE));
END;
/

The calls pick three different bodies. Rules for overloading:

  • The parameter lists must differ in number, order or type family. Differing only in the mode (IN vs OUT) or only in the return type is illegal.
  • Types in the same family are ambiguous. NUMBER and INTEGER, or VARCHAR2(10) and VARCHAR2(50), can't be told apart, and the compiler raises PLS-00307: too many declarations match.
  • With default values, overlapping signatures can produce ambiguity. Keep overloaded versions clearly different.

The Python stand-in dispatches by argument type, the idea behind resolution:

Python Overload resolution by type (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Public and private variables, and constants

A variable in the spec is public: any code can read or write it, which is convenient and risky. A variable in the body is private, and the usual pattern is to expose it through a getter and setter so you can validate changes. Constants (c_max_raise CONSTANT NUMBER := 20) cannot change, so declaring them in the spec is safe and common. Prefer constants and functions over public variables, and document every piece of state a package keeps.

Forward declarations and cursors

Private subprograms must exist before they are referenced. If two private procedures call each other, give one a forward declaration, the header with a semicolon, above both:

SQL
PROCEDURE b;            -- forward declaration
PROCEDURE a IS BEGIN b; END;
PROCEDURE b IS BEGIN NULL; END;

Packages can also hold explicit cursors. A cursor declared in the spec stays open across calls within the session if you do not close it, which is a classic cause of ORA-01000 leaks. Open and close within the same procedure unless you have a reason not to.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How long does a package-level variable keep its value by default?

    Package state is held in session memory. Each session has its own copy.
  2. 2. What does ROLLBACK do to a package variable?

    Only database changes are rolled back, not PL/SQL variables.
  3. 3. Which pair of overloads is illegal?

    Overloads must differ in number or type of parameters. A different mode alone does not distinguish them.
  4. 4. What does ORA-04068 tell you?

    Recompiling invalidates session state. The next call re-initializes and normally succeeds.

Summary

  • Package variables persist per session, are not transactional, and are lost when the package is recompiled (ORA-04068).
  • An initialization block runs once per session on first use; keep it quick and safe.
  • Overloading is allowed in packages when parameter types or counts differ; same-family types are ambiguous.
  • Prefer constants and getter functions to public variables.

Next: DML Triggers