Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 38 ยท Projects and Practice

Project: A Bank Ledger with PL/SQL

Build a small double-entry bank ledger in Oracle: tables and constraints, a package with deposit and transfer, an audit trigger, error handling, plus a runnable SQLite version.

This project ties the chapter together. You will design a tiny bank ledger in Oracle SQL and PL/SQL: tables with constraints, a package exposing deposit, withdraw and transfer, exceptions for business rules, a trigger that writes an audit trail, and transaction control that keeps balances correct even when something fails.

Oracle cannot run on this site, so the Oracle code is read-only, written to run unchanged on Oracle 19c, 21c or 23ai in SQL Developer or SQL*Plus. The runnable parts are a SQLite stand-in of the schema, triggers and transactions, and a Python model of the transfer rules.

Requirements

  1. Customers own accounts, each with a balance that can never go below zero.
  2. Every change of money is recorded as an immutable ledger entry (a positive amount for deposits, negative for withdrawals).
  3. A transfer moves money between two accounts atomically: both entries or neither.
  4. Business errors have clear codes. Callers (not the package) decide when to COMMIT.
  5. Direct updates of balances are audited.

Step 1: tables and constraints

SQL
CREATE TABLE accounts (
  account_id  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  owner_name  VARCHAR2(100) NOT NULL,
  balance     NUMBER(12,2)  DEFAULT 0 NOT NULL,
  status      VARCHAR2(10)  DEFAULT 'OPEN' NOT NULL,
  CONSTRAINT accounts_balance_ck CHECK (balance >= 0),
  CONSTRAINT accounts_status_ck  CHECK (status IN ('OPEN', 'FROZEN', 'CLOSED'))
);

CREATE TABLE ledger_entries (
  entry_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id  NUMBER NOT NULL REFERENCES accounts (account_id),
  amount      NUMBER(12,2) NOT NULL,
  description VARCHAR2(200),
  created_at  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT ledger_amount_ck CHECK (amount <> 0)
);

CREATE INDEX ledger_entries_acct_ix ON ledger_entries (account_id, created_at);   -- foreign key index

The CHECK (balance >= 0) constraint is the last line of defense: even buggy code cannot overdraw an account. The index on the foreign key keeps joins and parent deletes cheap (see indexes).

Step 2: the package specification

SQL
CREATE OR REPLACE PACKAGE bank_api AS
  e_insufficient_funds EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_insufficient_funds, -20101);
  e_account_unavailable EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_account_unavailable, -20102);

  PROCEDURE deposit  (p_account_id IN NUMBER, p_amount IN NUMBER);
  PROCEDURE withdraw (p_account_id IN NUMBER, p_amount IN NUMBER);
  PROCEDURE transfer (p_from_id IN NUMBER, p_to_id IN NUMBER, p_amount IN NUMBER);
  FUNCTION  balance_of (p_account_id IN NUMBER) RETURN NUMBER;
END bank_api;
/

Step 3: the package body

SQL
CREATE OR REPLACE PACKAGE BODY bank_api AS

  PROCEDURE lock_open_account (p_id IN NUMBER, p_balance OUT NUMBER) IS
    v_status accounts.status%TYPE;
  BEGIN
    SELECT balance, status INTO p_balance, v_status
      FROM accounts WHERE account_id = p_id FOR UPDATE;
    IF v_status <> 'OPEN' THEN
      RAISE_APPLICATION_ERROR(-20102, 'Account ' || p_id || ' is ' || v_status);
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20102, 'Account ' || p_id || ' does not exist');
  END lock_open_account;

  PROCEDURE post (p_id IN NUMBER, p_amount IN NUMBER, p_text IN VARCHAR2) IS
  BEGIN
    UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_id;
    INSERT INTO ledger_entries (account_id, amount, description)
    VALUES (p_id, p_amount, p_text);
  END post;

  PROCEDURE deposit (p_account_id IN NUMBER, p_amount IN NUMBER) IS
    v_balance NUMBER;
  BEGIN
    IF p_amount IS NULL OR p_amount <= 0 THEN
      RAISE_APPLICATION_ERROR(-20103, 'Deposit must be positive');
    END IF;
    lock_open_account(p_account_id, v_balance);
    post(p_account_id, p_amount, 'Deposit');
  END deposit;

  PROCEDURE withdraw (p_account_id IN NUMBER, p_amount IN NUMBER) IS
    v_balance NUMBER;
  BEGIN
    IF p_amount IS NULL OR p_amount <= 0 THEN
      RAISE_APPLICATION_ERROR(-20103, 'Withdrawal must be positive');
    END IF;
    lock_open_account(p_account_id, v_balance);
    IF p_amount > v_balance THEN
      RAISE_APPLICATION_ERROR(-20101, 'Insufficient funds: balance ' || v_balance);
    END IF;
    post(p_account_id, -p_amount, 'Withdrawal');
  END withdraw;

  PROCEDURE transfer (p_from_id IN NUMBER, p_to_id IN NUMBER, p_amount IN NUMBER) IS
    v_balance NUMBER;
    v_low  NUMBER := LEAST(p_from_id, p_to_id);
    v_high NUMBER := GREATEST(p_from_id, p_to_id);
  BEGIN
    IF p_from_id = p_to_id THEN
      RAISE_APPLICATION_ERROR(-20104, 'Cannot transfer to the same account');
    END IF;
    -- lock both accounts in id order to avoid deadlocks
    lock_open_account(v_low,  v_balance);
    lock_open_account(v_high, v_balance);
    withdraw(p_from_id, p_amount);
    post(p_to_id, p_amount, 'Transfer from ' || p_from_id);
  END transfer;

  FUNCTION balance_of (p_account_id IN NUMBER) RETURN NUMBER IS
    v_balance accounts.balance%TYPE;
  BEGIN
    SELECT balance INTO v_balance FROM accounts WHERE account_id = p_account_id;
    RETURN v_balance;
  END balance_of;

END bank_api;
/

Study the design choices, each of which comes from an earlier lesson:

  • lock_open_account uses SELECT ... FOR UPDATE so two sessions cannot both pass the balance check (the lost-update problem in locking).
  • transfer locks the lower id first, so two opposite transfers cannot deadlock.
  • The package never commits. The caller owns the transaction (COMMIT, ROLLBACK and SAVEPOINT).
  • The calling error codes sit in the -20000 range and the package spec names two of them with PRAGMA EXCEPTION_INIT (user-defined exceptions).
  • Private helpers (lock_open_account, post) are not in the spec (packages).

Step 4: an audit trigger

SQL
CREATE TABLE balance_audit (
  audit_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id  NUMBER, old_balance NUMBER, new_balance NUMBER,
  changed_by  VARCHAR2(128), changed_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE OR REPLACE TRIGGER accounts_balance_audit
  AFTER UPDATE OF balance ON accounts
  FOR EACH ROW
  WHEN (NEW.balance <> OLD.balance)
BEGIN
  INSERT INTO balance_audit (account_id, old_balance, new_balance, changed_by)
  VALUES (:OLD.account_id, :OLD.balance, :NEW.balance, USER);
END;
/

Step 5: using it

SQL
BEGIN
  bank_api.deposit(1, 500);
  bank_api.transfer(1, 2, 200);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

The runnable version

The SQLite stand-in below builds the same tables, constraints and audit trigger, and runs a transfer inside a transaction. A failing transfer (the check constraint stops the overdraft) is rolled back as a whole.

SQL Ledger tables, trigger and a transfer (SQLite stand-in)

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

SQL An overdraft is rejected by the constraint (SQLite stand-in)

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

The business rules can also be checked in a small Python model before you port them to a test script. This one exercises each error path of transfer:

Python The transfer rules (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.

Extensions to try

  1. Add a statement function that returns a SYS_REFCURSOR of an account's entries between two dates (REF cursors).
  2. Add a daily_interest procedure that credits every open account with FORALL.
  3. Replace the audit trigger with a compound trigger that writes one summary row per statement.
  4. Log failures through an autonomous log_error procedure.
  5. Create a limited user teller that has only EXECUTE on bank_api and no table access (users and privileges).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does transfer lock the account with the lower id first?

    If every session locks accounts in the same order, no wait cycle can form.
  2. 2. Why does the package not call COMMIT?

    Helpers that commit make it impossible to group operations into one unit of work.
  3. 3. What does the CHECK (balance >= 0) constraint add on top of the package's checks?

    Declarative constraints are enforced for every statement, whoever runs it.
  4. 4. Which statement about the audit trigger is true?

    It is an AFTER row trigger with a WHEN condition comparing NEW and OLD.

Summary

  • A reliable ledger combines constraints (the safety net), a package (the interface), row locks (consistency), exceptions (clear errors) and a trigger (the audit trail).
  • Keep commits with the caller, lock in a fixed order, and index foreign keys.
  • The runnable stand-ins prove the schema, trigger and transaction behavior; the Oracle package is the same logic in PL/SQL.

Next: Project: Inventory and Order Processing