Explain package-level (global) variables and their session-persistent state in PL/SQL

How it works

A variable declared in a package spec or body — outside any procedure or function — persists for the life of the database session, not just one call. It's initialized once, the first time the package is referenced in a session, and every subsequent call within that same session sees whatever the last call left it as. That makes packages a natural place for per-session caching or state, though that state isn't shared across different sessions and needs care in a connection-pooled environment.

CREATE OR REPLACE PACKAGE session_ctx AS
  g_current_user_id NUMBER;
  PROCEDURE set_user(p_user_id NUMBER);
END session_ctx;
/

CREATE OR REPLACE PACKAGE BODY session_ctx AS
  PROCEDURE set_user(p_user_id NUMBER) IS
  BEGIN
    g_current_user_id := p_user_id;
  END;
END session_ctx;
/
-- Any procedure in this session can now read session_ctx.g_current_user_id
-- after set_user has been called once.

Related Questions