PL/SQL packages, session state and ORA-04068

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- One package in two pieces: a specification that declares three public items, and a body that hides a private
-- lookup, two state variables and an initialisation part. Needs the practice schema (run the schema_oracle.sql of
-- practice-schema-and-dialect-basics first). examples/model/session_state_model.py prints the same counters.
-- The script drops the package at the end, so it can run again and leaves the schema as it found it.
SET SERVEROUTPUT ON

-- The specification: everything a caller needs, and nothing about how it works.
CREATE OR REPLACE PACKAGE account_api AUTHID DEFINER IS
  FUNCTION balance_of (p_account_id IN NUMBER) RETURN NUMBER;
  FUNCTION lookups_so_far RETURN PLS_INTEGER;
  PROCEDURE forget_cache;
END account_api;
/

-- The body: the private helper and the two variables are invisible from outside.
CREATE OR REPLACE PACKAGE BODY account_api IS
  g_lookups        PLS_INTEGER;                   -- package state, one copy per session
  g_cached_id      accounts.account_id%TYPE;
  g_cached_balance accounts.balance%TYPE;

  FUNCTION read_balance (p_account_id IN NUMBER)  -- private: declared here, not in the specification
    RETURN NUMBER
  IS
    v_balance accounts.balance%TYPE;
  BEGIN
    SELECT balance INTO v_balance
      FROM accounts
     WHERE account_id = p_account_id;
    g_lookups := g_lookups + 1;
    RETURN v_balance;
  END read_balance;

  FUNCTION balance_of (p_account_id IN NUMBER)
    RETURN NUMBER
  IS
  BEGIN
    IF g_cached_id IS NULL OR g_cached_id <> p_account_id THEN
      g_cached_balance := read_balance(p_account_id);
      g_cached_id      := p_account_id;
      DBMS_OUTPUT.PUT_LINE('read from the table');
    ELSE
      DBMS_OUTPUT.PUT_LINE('answered from the cache');
    END IF;
    RETURN g_cached_balance;
  END balance_of;

  FUNCTION lookups_so_far RETURN PLS_INTEGER IS
  BEGIN
    RETURN g_lookups;
  END lookups_so_far;

  PROCEDURE forget_cache IS
  BEGIN
    g_cached_id      := NULL;
    g_cached_balance := NULL;
  END forget_cache;
BEGIN                                             -- the initialisation part: the first reference runs it
  g_lookups   := 0;
  g_cached_id := NULL;
  DBMS_OUTPUT.PUT_LINE('account_api: initialised for this session');
END account_api;
/

-- Two calls for the same account, then the counter the specification exposes.
BEGIN
  DBMS_OUTPUT.PUT_LINE('balance_of(3) = ' || TO_CHAR(account_api.balance_of(3), 'FM999990.00'));
  DBMS_OUTPUT.PUT_LINE('balance_of(3) = ' || TO_CHAR(account_api.balance_of(3), 'FM999990.00'));
  DBMS_OUTPUT.PUT_LINE('lookups_so_far = ' || account_api.lookups_so_far);
END;
/
-- By the documented behaviour this prints, in order:
--   account_api: initialised for this session   (the initialisation part, once in this session)
--   read from the table
--   balance_of(3) = 760.25
--   answered from the cache
--   balance_of(3) = 760.25
--   lookups_so_far = 1                          (one table read, two calls)

-- A caller cannot reach the private parts: both of these fail to compile with PLS-00302.
BEGIN
  DBMS_OUTPUT.PUT_LINE(account_api.read_balance(3));
END;
/
BEGIN
  DBMS_OUTPUT.PUT_LINE(account_api.g_lookups);
END;
/
-- Expected for each block: PLS-00302: component 'READ_BALANCE' (then 'G_LOOKUPS') must be declared.

DROP PACKAGE account_api;

"""account_pkg.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("packages/account_pkg.sql", errors=2)

$ sqlplus -s lab@//localhost/FREEPDB1 @packages/account_pkg.sql
Package created.


Package body created.

account_api: initialised for this session
read from the table
balance_of(3) = 760.25
answered from the cache
balance_of(3) = 760.25
lookups_so_far = 1

PL/SQL procedure successfully completed.

  DBMS_OUTPUT.PUT_LINE(account_api.read_balance(3));
                                   *
ERROR at line 2:
ORA-06550: line 2, column 36:
PLS-00302: component 'READ_BALANCE' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
Help: https://docs.oracle.com/error-help/db/ora-06550/


  DBMS_OUTPUT.PUT_LINE(account_api.g_lookups);
                                   *
ERROR at line 2:
ORA-06550: line 2, column 36:
PLS-00302: component 'G_LOOKUPS' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
Help: https://docs.oracle.com/error-help/db/ora-06550/



Package dropped.

"""One package, two sessions: Oracle's instantiation rules, modelled.

The model replays account_pkg.sql in two database sessions over the practice
accounts (3 holds 760.25 and 4 holds 41250.50). It applies the documented
rules, so nothing here is a recorded database run.

The rules (PL/SQL Language Reference 26ai, Package Instantiation and
Initialization, and Package State):
1. The first time a session references a package item, Oracle instantiates
   the package for that session. Every session gets its own instantiation.
2. Instantiating it initialises it: initial values for public constants,
   then for public variables that declare one, then the initialisation part
   of the body.
3. The values of the variables, constants and cursors a package declares
   are its package state, and a stateful instantiation keeps its own copy
   for the life of the session.

Edit CALLS to give the sessions other accounts, or call forget_cache, and
run it again: each session's counter follows only its own calls.
"""
from decimal import Decimal

BALANCE = {              # the practice accounts, account_id -> balance
    1: Decimal("2500.00"),
    2: Decimal("18000.00"),
    3: Decimal("760.25"),
    4: Decimal("41250.50"),
}
CALLS = [                # (session, call) in the order the two clients run them
    ("session A", "balance_of(3)"),
    ("session A", "balance_of(3)"),
    ("session B", "balance_of(3)"),
    ("session A", "balance_of(4)"),
    ("session B", "lookups_so_far"),
    ("session A", "lookups_so_far"),
]


class Instantiation:
    """One session's copy of the package state, as the body declares it."""

    def __init__(self, session):
        self.session = session
        self.lookups = 0            # g_lookups
        self.cached_id = None       # g_cached_id
        self.cached_balance = None  # g_cached_balance

    def balance_of(self, account_id):
        """The public function: a table read, or the cached answer."""
        if self.cached_id != account_id:
            self.cached_balance = BALANCE[account_id]
            self.cached_id = account_id
            self.lookups += 1
            return self.cached_balance, "read from the table"
        return self.cached_balance, "answered from the cache"


def reference(sessions, session):
    """Returns the session's instantiation, creating it on first reference."""
    if session in sessions:
        return sessions[session]
    print(f"  {session}: no instantiation yet, so Oracle makes one")
    print("    initialisation part: g_lookups 0, g_cached_id NULL")
    print("    prints: account_api: initialised for this session")
    sessions[session] = Instantiation(session)
    return sessions[session]


def run(calls):
    """Runs the calls in order and reports what each session sees."""
    sessions = {}
    for session, call in calls:
        print(f"{session} calls account_api.{call}")
        state = reference(sessions, session)
        if call == "lookups_so_far":
            print(f"  returns {state.lookups}")
            continue
        account_id = int(call[len("balance_of("):-1])
        balance, how = state.balance_of(account_id)
        print(f"  prints: {how}")
        print(f"  returns {balance}")
    print()
    print("At the end of the two clients' work:")
    for session, state in sessions.items():
        print(f"  {session}: lookups {state.lookups}, cached account "
              f"{state.cached_id}")


run(CALLS)
total = sum(1 for session, _ in CALLS if session == "session A")
print(f"session A made {total} of the {len(CALLS)} calls; neither session "
      "touched the other's state.")

session A calls account_api.balance_of(3)
  session A: no instantiation yet, so Oracle makes one
    initialisation part: g_lookups 0, g_cached_id NULL
    prints: account_api: initialised for this session
  prints: read from the table
  returns 760.25
session A calls account_api.balance_of(3)
  prints: answered from the cache
  returns 760.25
session B calls account_api.balance_of(3)
  session B: no instantiation yet, so Oracle makes one
    initialisation part: g_lookups 0, g_cached_id NULL
    prints: account_api: initialised for this session
  prints: read from the table
  returns 760.25
session A calls account_api.balance_of(4)
  prints: read from the table
  returns 41250.50
session B calls account_api.lookups_so_far
  returns 1
session A calls account_api.lookups_so_far
  returns 2

At the end of the two clients' work:
  session A: lookups 2, cached account 4
  session B: lookups 1, cached account 3
session A made 4 of the 6 calls; neither session touched the other's state.