PL/pgSQL Exception Handling Doesn't Behave Like PL/SQL — Edge Cases

The Surface Similarity That Masks the Differences

Both PL/SQL and PL/pgSQL use BEGIN ... EXCEPTION ... END blocks. Both support named exception conditions. Both allow you to catch specific error types and handle or re-raise them. A migrated procedure that catches DUP_VAL_ON_INDEX / unique_violation and logs a message looks syntactically similar in both languages. It may even pass functional testing in a non-production environment.

The behavioral differences surface under specific conditions: when DML precedes the exception that gets caught, when exceptions are nested inside loops, when the handler itself performs DML, and when errors need to propagate up the call stack with their original context intact. These are exactly the conditions that appear in real production procedures.

Difference 1: DML Before the Exception Is Rolled Back

This is the most consequential behavioral difference and the most common source of post-migration data integrity bugs.

In Oracle PL/SQL, when an exception is caught in an EXCEPTION block, DML statements that executed before the exception within the same BEGIN block are not automatically rolled back. They remain in the transaction's write set, pending the final COMMIT or ROLLBACK of the enclosing transaction. The EXCEPTION block catches the error and continues — the prior DML stands.

-- Oracle PL/SQL behavior
CREATE OR REPLACE PROCEDURE process_order(p_order_id NUMBER) AS
BEGIN
  UPDATE orders SET status = 'PROCESSING' WHERE order_id = p_order_id;
  -- ^ this UPDATE stays in the transaction

  INSERT INTO order_audit(order_id, action, ts)
  VALUES (p_order_id, 'PROCESSING', SYSDATE);
  -- ^ this INSERT also stays

  -- This next statement fails
  INSERT INTO inventory_hold(order_id, qty)
  SELECT p_order_id, quantity FROM order_lines
  WHERE order_id = p_order_id AND qty > 9999;
  -- raises ORA-01400: cannot insert NULL if qty is null

EXCEPTION
  WHEN OTHERS THEN
    -- In Oracle: the two prior DML statements are still in the transaction
    -- They will commit if the caller commits
    INSERT INTO error_log(order_id, error_msg)
    VALUES (p_order_id, SQLERRM);
    -- no RAISE here = exception is swallowed, procedure returns normally
END;

In PL/pgSQL, the EXCEPTION block implicitly wraps the BEGIN block in a subtransaction (a PostgreSQL savepoint). When an exception is caught, PostgreSQL rolls back the subtransaction — which includes all DML that executed before the exception within that block.

-- PL/pgSQL behavior — different from Oracle
CREATE OR REPLACE FUNCTION process_order(p_order_id integer)
RETURNS void AS $$
BEGIN
  UPDATE orders SET status = 'PROCESSING' WHERE order_id = p_order_id;
  -- ^ this UPDATE will be ROLLED BACK if an exception is caught below

  INSERT INTO order_audit(order_id, action, ts)
  VALUES (p_order_id, 'PROCESSING', now());
  -- ^ this INSERT will also be ROLLED BACK

  INSERT INTO inventory_hold(order_id, qty)
  SELECT p_order_id, quantity FROM order_lines
  WHERE order_id = p_order_id AND quantity > 9999;
  -- raises not_null_violation

EXCEPTION
  WHEN OTHERS THEN
    -- At this point, the UPDATE and INSERT above are already rolled back
    -- The subtransaction was rolled back when the exception was caught
    INSERT INTO error_log(order_id, error_msg)
    VALUES (p_order_id, SQLERRM);
    -- This INSERT is in the outer transaction, not the subtransaction
    -- It will commit if the caller commits
END;
$$ LANGUAGE plpgsql;

The practical consequence: migrated Oracle procedures that rely on pre-exception DML being preserved will silently drop those writes in PostgreSQL. The procedure appears to succeed (it does not raise to the caller), but the UPDATE and audit INSERT never happened.

Difference 2: No SQLCODE/-20000 User-Defined Errors

Oracle PL/SQL uses numeric error codes. User-defined application errors use the range -20000 to -20999, raised with RAISE_APPLICATION_ERROR. Exception handlers test error codes with SQLCODE:

-- Oracle: user-defined application errors
IF l_balance < 0 THEN
  RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds: balance is ' || l_balance);
END IF;

-- Oracle: catching by error code
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -20001 THEN
      -- handle insufficient funds
      NULL;
    ELSIF SQLCODE = -1 THEN
      -- ORA-00001: unique constraint violated
      NULL;
    END IF;

PL/pgSQL does not have RAISE_APPLICATION_ERROR or the -20xxx error code convention. User-defined errors use named exception conditions with SQLSTATE codes, and the error text is the primary carrier of application-level detail:

-- PL/pgSQL: user-defined errors
IF l_balance < 0 THEN
  RAISE EXCEPTION 'Insufficient funds: balance is %', l_balance
    USING ERRCODE = 'P0001';  -- use a custom SQLSTATE in the P0xxx range
END IF;

-- PL/pgSQL: catching errors
EXCEPTION
  WHEN SQLSTATE 'P0001' THEN
    -- handle application-defined error
    NULL;
  WHEN unique_violation THEN
    -- catch PostgreSQL named condition
    NULL;
  WHEN OTHERS THEN
    -- SQLSTATE is available as SQLSTATE variable
    -- SQLERRM is available as SQLERRM variable
    RAISE NOTICE 'Error %: %', SQLSTATE, SQLERRM;

Any migrated Oracle code that raises application errors via RAISE_APPLICATION_ERROR(-20xxx, ...) and catches them via SQLCODE comparison in EXCEPTION handlers needs a complete rework of the error communication pattern. This is common in larger Oracle applications with layered procedure call stacks where application-level errors flow up through multiple handler levels.

Difference 3: Re-Raise Semantics

In Oracle PL/SQL, a bare RAISE inside an EXCEPTION handler re-raises the current exception with its original error stack intact:

-- Oracle PL/SQL re-raise
EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;  -- re-raises with original stack

PL/pgSQL supports the same syntax — a bare RAISE; inside an exception handler re-raises the current exception. This part behaves identically. The difference is in what "original stack" means and how error context propagates.

In PL/pgSQL, when you re-raise, the calling function sees the exception with the original SQLSTATE and message, but the call stack detail (available via PG_EXCEPTION_CONTEXT in the GET STACKED DIAGNOSTICS interface) reflects the re-raise point, not the original raise point in some PostgreSQL versions. The behavior depends on the PostgreSQL version:

-- PL/pgSQL: accessing full error context
EXCEPTION
  WHEN OTHERS THEN
    DECLARE
      v_state   text;
      v_msg     text;
      v_detail  text;
      v_hint    text;
      v_context text;
    BEGIN
      GET STACKED DIAGNOSTICS
        v_state   = RETURNED_SQLSTATE,
        v_msg     = MESSAGE_TEXT,
        v_detail  = PG_EXCEPTION_DETAIL,
        v_hint    = PG_EXCEPTION_HINT,
        v_context = PG_EXCEPTION_CONTEXT;

      INSERT INTO error_log(sqlstate, message, detail, context, logged_at)
      VALUES (v_state, v_msg, v_detail, v_context, now());

      RAISE;  -- re-raise original exception
    END;

The Oracle equivalent — DBMS_UTILITY.FORMAT_ERROR_BACKTRACE — is functionally similar to PG_EXCEPTION_CONTEXT but the format and contents differ. Migrated error logging procedures that parse the Oracle backtrace format will need to be updated.

Difference 4: Nested Exception Blocks and Loop Behavior

Oracle PL/SQL allows nested BEGIN...EXCEPTION...END blocks inside loops, with each inner block creating its own exception scope. This is commonly used in batch processing loops where you want to skip failed rows and continue:

-- Oracle: loop with per-row exception handling
FOR rec IN (SELECT order_id FROM orders WHERE status = 'PENDING') LOOP
  BEGIN
    process_single_order(rec.order_id);
    -- if this raises, only this iteration is affected
  EXCEPTION
    WHEN OTHERS THEN
      log_failed_order(rec.order_id, SQLERRM);
      -- continue to next iteration
  END;
END LOOP;

PL/pgSQL supports the same nested block structure and the behavior is equivalent for most cases. However, the subtransaction semantics apply at every level: each inner BEGIN block with an EXCEPTION clause creates a savepoint. In tight loops processing thousands of rows, creating and releasing savepoints for every iteration has a measurable performance cost that does not exist in Oracle.

-- PL/pgSQL: same pattern, but savepoint overhead per iteration
FOR rec IN SELECT order_id FROM orders WHERE status = 'PENDING' LOOP
  BEGIN
    PERFORM process_single_order(rec.order_id);
  EXCEPTION
    WHEN OTHERS THEN
      INSERT INTO failed_orders(order_id, error_msg, failed_at)
      VALUES (rec.order_id, SQLERRM, now());
  END;
END LOOP;
-- Each loop iteration creates and releases a savepoint
-- At 100,000 iterations, this overhead is measurable

For high-volume batch processing in PL/pgSQL, consider processing errors via a different mechanism — such as a separate validation pass or wrapping the entire loop in a single exception handler with a re-try strategy — rather than per-iteration exception blocks.

Difference 5: NO_DATA_FOUND Behavior

Oracle raises NO_DATA_FOUND when a SELECT INTO statement returns no rows. PL/pgSQL raises no_data_found for the same condition, and the behavior in a FETCH from a cursor that has returned all rows also triggers it. This part is equivalent.

The difference is with implicit cursors in FOR loops. In Oracle, a FOR loop that selects zero rows simply does not execute the loop body — it does not raise NO_DATA_FOUND. In PL/pgSQL, the same is true. Both are consistent here.

Where Oracle differs: NO_DATA_FOUND in Oracle is also raised when you reference an undefined element in an associative array (INDEX BY table). PostgreSQL arrays do not raise an equivalent — an out-of-bounds array access raises array_subscript_error. Migrated code that uses Oracle associative arrays mapped to PostgreSQL arrays needs specific attention around error handling for missing keys.

Migration Checklist: Exception Handling Review

When auditing PL/SQL procedures for migration, flag every procedure that matches any of these patterns for manual exception handling review:

• EXCEPTION WHEN OTHERS block that does not re-raise, where DML precedes the failing statement — check whether pre-exception DML must be preserved
• RAISE_APPLICATION_ERROR usage — requires redesign of the error communication pattern
• SQLCODE comparisons in EXCEPTION handlers — all numeric error codes need mapping to PostgreSQL SQLSTATE equivalents
• DBMS_UTILITY.FORMAT_ERROR_BACKTRACE — replace with GET STACKED DIAGNOSTICS / PG_EXCEPTION_CONTEXT
• Nested exception blocks inside high-volume loops — assess savepoint overhead impact
• Associative array element access inside exception handlers — verify array bounds behavior

These patterns are present in virtually every Oracle application with meaningful stored procedure logic. They are not exotic edge cases. They are the normal way Oracle applications handle errors, and each one requires explicit attention during migration — not just a syntax conversion.