Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 38 ยท Projects and Practice

Project: Inventory and Order Processing in Oracle

Build an order-processing module in Oracle: stock and orders tables, a package that reserves stock safely, a stock-history trigger, a bulk price import with error capture and a report.

The second project is closer to a typical business application. A shop keeps products with stock levels, customers place orders with several lines, and the system must never sell what it does not have. You will build the schema, a package that places orders atomically, a trigger that keeps a stock history, a bulk price import that survives bad rows, and a report query. Each piece reuses a technique from this chapter.

Oracle cannot run on this site. The Oracle code is read-only and targets Oracle 19c or later. The runnable parts are SQLite and Python stand-ins that reproduce the schema, the triggers, the transaction behavior and the bulk-import idea.

The design

The data model

products Price and stock on hand
orders One row per order
order_items Product, quantity, price
stock_history Every stock change

Stock changes are logged by a trigger, so no code path can forget.

1 / 2
SQL
CREATE TABLE products (
  product_id  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         VARCHAR2(30) NOT NULL UNIQUE,
  name        VARCHAR2(100) NOT NULL,
  price       NUMBER(10,2) NOT NULL CHECK (price >= 0),
  stock_qty   NUMBER(8)    DEFAULT 0 NOT NULL CHECK (stock_qty >= 0)
);

CREATE TABLE orders (
  order_id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  NUMBER NOT NULL,
  ordered_at   TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  status       VARCHAR2(10) DEFAULT 'NEW' NOT NULL
);

CREATE TABLE order_items (
  order_id    NUMBER NOT NULL REFERENCES orders (order_id),
  line_no     NUMBER NOT NULL,
  product_id  NUMBER NOT NULL REFERENCES products (product_id),
  qty         NUMBER(6) NOT NULL CHECK (qty > 0),
  unit_price  NUMBER(10,2) NOT NULL,
  PRIMARY KEY (order_id, line_no)
);
CREATE INDEX order_items_product_ix ON order_items (product_id);

CREATE TABLE stock_history (
  history_id  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id  NUMBER NOT NULL,
  old_qty     NUMBER, new_qty NUMBER,
  changed_at  TIMESTAMP DEFAULT SYSTIMESTAMP
);

The trigger: a stock history nobody can skip

SQL
CREATE OR REPLACE TRIGGER products_stock_hist
  AFTER UPDATE OF stock_qty ON products
  FOR EACH ROW
  WHEN (NEW.stock_qty <> OLD.stock_qty)
BEGIN
  INSERT INTO stock_history (product_id, old_qty, new_qty)
  VALUES (:OLD.product_id, :OLD.stock_qty, :NEW.stock_qty);
END;
/

The SQLite version below creates the schema and the trigger, then places one order inside a transaction. Notice the history rows appear without the order code mentioning them.

SQL Schema, stock trigger and an order (SQLite stand-in)

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

The order package

The package accepts an order as a collection of lines, checks and reduces stock, and writes the order rows. It does not commit.

SQL
CREATE OR REPLACE PACKAGE order_api AS
  TYPE t_line IS RECORD (product_id NUMBER, qty NUMBER);
  TYPE t_lines IS TABLE OF t_line INDEX BY PLS_INTEGER;

  FUNCTION place_order (p_customer_id IN NUMBER, p_lines IN t_lines) RETURN NUMBER;
END order_api;
/

CREATE OR REPLACE PACKAGE BODY order_api AS

  FUNCTION place_order (p_customer_id IN NUMBER, p_lines IN t_lines) RETURN NUMBER IS
    v_order_id orders.order_id%TYPE;
    v_price    products.price%TYPE;
    v_stock    products.stock_qty%TYPE;
  BEGIN
    IF p_lines.COUNT = 0 THEN
      RAISE_APPLICATION_ERROR(-20201, 'An order needs at least one line');
    END IF;

    INSERT INTO orders (customer_id) VALUES (p_customer_id)
      RETURNING order_id INTO v_order_id;

    FOR i IN p_lines.FIRST .. p_lines.LAST LOOP
      BEGIN
        SELECT price, stock_qty INTO v_price, v_stock
          FROM products WHERE product_id = p_lines(i).product_id FOR UPDATE;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RAISE_APPLICATION_ERROR(-20202, 'Unknown product ' || p_lines(i).product_id);
      END;

      IF p_lines(i).qty > v_stock THEN
        RAISE_APPLICATION_ERROR(-20203,
          'Only ' || v_stock || ' of product ' || p_lines(i).product_id || ' in stock');
      END IF;

      UPDATE products SET stock_qty = stock_qty - p_lines(i).qty
       WHERE product_id = p_lines(i).product_id;

      INSERT INTO order_items (order_id, line_no, product_id, qty, unit_price)
      VALUES (v_order_id, i, p_lines(i).product_id, p_lines(i).qty, v_price);
    END LOOP;

    RETURN v_order_id;
  END place_order;

END order_api;
/

The RETURNING ... INTO clause fetches the generated identity value without a second query. Because any error raised in the middle propagates to the caller, who rolls back, the stock reduction for earlier lines is undone too. If the caller catches the error and chooses to keep going, a SAVEPOINT taken before the call lets it undo just this order.

The Python model below puts a savepoint-style snapshot around each order, so a failure in line 2 of an order restores line 1's stock:

Python All-or-nothing order lines (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.

Bulk price import with error capture

Suppliers send a price file with thousands of rows, some of them bad. One bad row must not reject the file, and you want a list of what failed. In Oracle this is FORALL ... SAVE EXCEPTIONS (see bulk processing):

SQL
CREATE OR REPLACE PROCEDURE import_prices (
  p_skus   IN SYS.ODCIVARCHAR2LIST,
  p_prices IN SYS.ODCINUMBERLIST
) IS
  e_bulk EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk, -24381);
BEGIN
  FORALL i IN 1 .. p_skus.COUNT SAVE EXCEPTIONS
    UPDATE products SET price = p_prices(i) WHERE sku = p_skus(i);
EXCEPTION
  WHEN e_bulk THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Row ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ': ' ||
        SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
    END LOOP;
END;
/

A negative price fails the CHECK (price >= 0) constraint; the other rows are updated. An update that matches no SKU is not an error at all, so also test SQL%BULK_ROWCOUNT(i) = 0 to report unknown SKUs.

Python Import with per-row results (Python and SQLite 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 report

A report joins the tables and aggregates. Analytic functions rank the best sellers without a self-join:

SQL
SELECT p.sku, p.name,
       SUM(oi.qty)                        AS units_sold,
       SUM(oi.qty * oi.unit_price)        AS revenue,
       RANK() OVER (ORDER BY SUM(oi.qty * oi.unit_price) DESC) AS revenue_rank
  FROM products p
  JOIN order_items oi ON oi.product_id = p.product_id
 GROUP BY p.sku, p.name
 ORDER BY revenue_rank;
SQL Best-seller report with RANK (SQLite stand-in)

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

Extensions to try

  1. Add an order status workflow (NEW, PAID, SHIPPED, CANCELLED) with a trigger that rejects illegal jumps, and restock items on cancel (DML triggers).
  2. Create a view order_totals and an INSTEAD OF trigger so a front end can insert whole orders (INSTEAD OF triggers).
  3. Add a function-based index for case-insensitive product name search and compare plans (execution plans).
  4. Use SKIP LOCKED to build a worker that picks NEW orders for packing without blocking other workers (locking).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is the stock check done after SELECT ... FOR UPDATE on the product?

    The lock serializes access to the product row until the transaction ends.
  2. 2. What does RETURNING order_id INTO v_order_id give you?

    RETURNING hands back column values of the affected row directly.
  3. 3. In a FORALL ... SAVE EXCEPTIONS import, which case is NOT reported as an exception?

    An update that matches zero rows succeeds; check SQL%BULK_ROWCOUNT(i) = 0 to find those.
  4. 4. Why is stock history written by a trigger rather than by the order package?

    A trigger cannot be bypassed by code paths that forget to log.

Summary

  • Constraints plus row locks plus a package that never commits give safe, atomic order placement.
  • A trigger guarantees the stock history; RETURNING INTO and identity columns remove manual id handling.
  • Bulk imports use FORALL ... SAVE EXCEPTIONS and SQL%BULK_ROWCOUNT to report every bad row without stopping.
  • Analytic functions such as RANK() OVER build reports in one pass.

Next: Practice: PL/SQL Exercises