Oracle Notebook
SQL & PL/SQL · 7 min read

PL/SQL Patterns That Pay Off in Real Projects

Eight practical PL/SQL patterns that consistently make production code faster, safer, and easier to maintain — with examples.

PL/SQL has a reputation problem. Newcomers see it as legacy. Veterans defend it. Both are partly right. PL/SQL is a perfectly reasonable place to put data-intensive logic — but only if you write it like someone who’s done it before.

This post is a tour of patterns that consistently improve real production PL/SQL: faster, safer, easier to maintain. Nothing exotic. Just the things that pay off every time.

Why PL/SQL still matters

PL/SQL runs inside the database. That sounds obvious until you remember what it means: zero network round-trips between code and data, direct access to row-level operations, and the optimizer treating your procedural code as data it can reason about. For data-heavy workloads — ETL, batch processing, reporting feeders — well-written PL/SQL is faster and simpler than the equivalent application-tier code.

The patterns below assume you’re already past “should we use PL/SQL at all” and are working on “how do we make this PL/SQL not suck.”

Pattern 1: BULK COLLECT and FORALL

The first and biggest win, and the one most often missed.

Naive row-by-row processing:

FOR rec IN (SELECT id FROM orders WHERE pending = 'Y') LOOP
  UPDATE order_history SET processed = 'Y' WHERE order_id = rec.id;
END LOOP;

The cursor loop forces Oracle to switch between the SQL and PL/SQL engines on every iteration. For thousands of rows, that’s thousands of context switches.

The same logic with BULK COLLECT and FORALL:

DECLARE
  TYPE id_t IS TABLE OF orders.id%TYPE;
  l_ids id_t;
BEGIN
  SELECT id BULK COLLECT INTO l_ids
  FROM   orders WHERE pending = 'Y';

  FORALL i IN 1 .. l_ids.COUNT
    UPDATE order_history SET processed = 'Y'
    WHERE  order_id = l_ids(i);
END;

One context switch per bulk operation, not one per row. For batches of any meaningful size, expect 5–10× speedups. For very large batches, cap the collection size with LIMIT and process in chunks of, say, 10,000 to bound memory.

Pattern 2: SAVE EXCEPTIONS for partial-failure batches

FORALL is fast, but a single failing row kills the batch. SAVE EXCEPTIONS lets you continue and inspect failures after:

BEGIN
  FORALL i IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
    UPDATE order_history SET processed = 'Y'
    WHERE  order_id = l_ids(i);

EXCEPTION WHEN OTHERS THEN
  IF SQLCODE = -24381 THEN  -- bulk errors
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        'Row ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
        ' failed: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
    END LOOP;
  ELSE
    RAISE;
  END IF;
END;

This is the right shape for ETL jobs where you want bad rows quarantined and good rows committed. Without SAVE EXCEPTIONS, one corrupt row reverts the entire batch.

Pattern 3: Pipelined table functions

When you need to return a streaming result set from a function — usable in a FROM clause — pipelined functions yield rows incrementally instead of building the full collection in memory.

CREATE TYPE order_summary AS OBJECT (
  order_id NUMBER,
  total    NUMBER
);

CREATE TYPE order_summary_tab AS TABLE OF order_summary;

CREATE FUNCTION get_summaries
  RETURN order_summary_tab PIPELINED
IS
BEGIN
  FOR rec IN (SELECT id, SUM(amount) total FROM orders GROUP BY id) LOOP
    PIPE ROW(order_summary(rec.id, rec.total));
  END LOOP;
  RETURN;
END;

Used as:

SELECT * FROM TABLE(get_summaries);

Particularly useful for ETL pipelines where downstream queries consume the function output incrementally — there’s no need to materialize the full set first. Memory stays bounded regardless of result size.

Pattern 4: Packages over standalone procedures

Standalone procedures and functions are fine for one-offs. For anything you’ll touch more than once, group related logic into packages.

Benefits:

  • A single point of recompilation. Changing a private procedure inside a package doesn’t invalidate dependents the way changing a standalone procedure does.
  • Private state. Package-level variables persist for the session, useful for caching configuration or accumulating batch state.
  • Namespace clarity. orders_pkg.calculate_total reads better than calculate_order_total.

Use a package-level g_log_level variable plus a set_log_level procedure rather than passing logging flags through every signature in your API.

Pattern 5: DETERMINISTIC functions

If a function returns the same output for the same input, mark it DETERMINISTIC:

CREATE FUNCTION normalize_phone (p_in VARCHAR2)
  RETURN VARCHAR2 DETERMINISTIC
IS ...

Oracle can then cache results within a query and use the function in function-based indexes. The catch: lying to Oracle here — claiming determinism when it doesn’t hold — produces incorrect results that are hard to debug. Only use it when it’s actually true.

A common mistake is marking functions deterministic when they reference session settings (like NLS_DATE_FORMAT) or query other tables. Both make the function non-deterministic even if it “feels” pure.

Pattern 6: Result cache for expensive functions

For functions that are expensive but called repeatedly with similar inputs across sessions, the SQL/PLSQL result cache can dramatically reduce execution count:

CREATE FUNCTION get_currency_rate (p_currency VARCHAR2, p_date DATE)
  RETURN NUMBER RESULT_CACHE
IS ...

Best for functions whose underlying data changes infrequently. Oracle automatically invalidates the cache when referenced tables change, so correctness isn’t a problem the way it is with deterministic. Performance is — the cache is shared across sessions, which is the whole point.

Pattern 7: Don’t catch and silently log

The most common PL/SQL anti-pattern is:

EXCEPTION WHEN OTHERS THEN
  log_error(SQLERRM);
END;

This swallows errors. The procedure “succeeds” from its caller’s perspective. Downstream code assumes success and proceeds with bad data. By the time someone notices, the original failure is buried in a log file from three weeks ago.

The right pattern is either to handle a specific exception you understand, or to re-raise after logging:

EXCEPTION WHEN OTHERS THEN
  log_error(SQLERRM);
  RAISE;
END;

Catch only when you can meaningfully recover. If you can’t, let the exception propagate — that’s the entire point of having exceptions.

Pattern 8: PRAGMA AUTONOMOUS_TRANSACTION for logging

Logging procedures that need to commit independently of the calling transaction — so the log persists even if the main transaction rolls back — should be marked autonomous:

PROCEDURE log_event(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO event_log(ts, msg) VALUES (SYSTIMESTAMP, p_msg);
  COMMIT;
END;

Use sparingly. Autonomous transactions are easy to misuse for “things that should commit immediately” when the right answer is restructuring the caller. The classic abuse: making an audit log autonomous because the main transaction sometimes rolls back. The fix is usually to log before the risky operation, not to escape the transaction entirely.

A few simple rules

  • Bulk anything you do in a loop.
  • Catch only what you can handle. Re-raise the rest.
  • Mark deterministic functions deterministic. Don’t mark non-deterministic ones deterministic.
  • Use packages for anything beyond a one-off.
  • Log to a table from an autonomous procedure, not from a wrapped WHEN OTHERS.
  • Profile before you optimize. PL/SQL’s hierarchical profiler (DBMS_HPROF) tells you where the time actually goes.

PL/SQL gets a lot done when written with these in mind. The patterns aren’t new — most have been in Oracle for over a decade. The reason they keep showing up in performance reviews is that newcomers re-discover them after writing the slow version first.

If you take one thing from this post: bulk operations. The single biggest delta between slow PL/SQL and fast PL/SQL, in real codebases, is whether the author has internalized BULK COLLECT and FORALL.