Oracle Database / Intermediate: application SQL and evidence / Lesson 10

PL/SQL blocks, cursors and error contracts

Combine procedural decisions with set-based SQL while keeping failure and transaction ownership explicit.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Read declaration, executable and exception sections of a block.
Use cursor attributes and propagate meaningful failures.

A block gives decisions a scope

PL/SQL adds variables, control flow and exceptions around SQL. An anonymous block is useful for explaining a local operation; stored procedures and packages give reusable database interfaces. DECLARE is optional, BEGIN starts executable statements, EXCEPTION introduces handlers and END closes the block. The slash used by SQL*Plus or SQLcl to execute the buffer is a client command, not part of the stored PL/SQL text sent through every connector. Label client-specific delimiters and output settings so learners do not paste them into a driver as if they were SQL grammar.

Validate a domain decision without hiding errors

The first block computes a bounded quote from two numeric inputs. RAISE_APPLICATION_ERROR uses an application error number in the documented -20000 to -20999 range. A negative weight should become a deliberate domain rejection rather than a misleading zero charge. DBMS_OUTPUT is a diagnostic buffer that a compatible client must enable and retrieve; it is not an HTTP response or durable audit log. The example assumes only the supplied local values and illustrates control flow. It is marked non-runnable because no Oracle runtime was used during authoring.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

DECLARE
  v_weight NUMBER := 3;
  v_rate NUMBER := 4;
  v_quote NUMBER;
BEGIN
  IF v_weight <= 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Weight must be positive');
  END IF;
  v_quote := v_weight * v_rate;
  DBMS_OUTPUT.PUT_LINE(v_quote);
END;

Expected output

With a client retrieving DBMS_OUTPUT, the logical printed value is 12. Changing weight to zero raises the application error instead.

A successful update can change no rows

SQL%ROWCOUNT describes the most recent relevant SQL statement, so capture or inspect it before another statement overwrites the context. The reservation block rejects unavailable capacity through a zero-row result. The predicate makes the capacity test and decrement one database operation; it does not separately read a value and then overwrite a potentially newer value. The caller owns COMMIT or ROLLBACK, allowing reservation to participate in a larger booking unit. A reusable helper that commits internally would make that composition impossible and could persist half of a business operation.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

BEGIN
  UPDATE capacity SET free_units = free_units - 2
  WHERE slot_id = 7 AND free_units >= 2;
  IF SQL%ROWCOUNT <> 1 THEN
    RAISE_APPLICATION_ERROR(-20002, 'Capacity reservation rejected');
  END IF;
END;

Exactly one matched row means the pending reservation succeeded. No COMMIT appears: the surrounding service controls the transaction.

Choose a cursor contract deliberately

SELECT ... INTO expects exactly one row and raises NO_DATA_FOUND for none or TOO_MANY_ROWS for more than one. Those exceptions are not interchangeable with a zero-row UPDATE. Use a cursor loop when several rows are an intended result, and prefer a set-based SQL transformation when one statement expresses the task clearly. Repeated row-by-row SQL can impose unnecessary context switches. Bulk collection and FORALL offer tools for appropriate cases, but they introduce collection bounds and partial-error semantics that must be designed explicitly rather than used as an unmeasured speed ritual.

A handler is part of the public contract

WHEN OTHERS THEN NULL conceals failures and can make a caller assume work succeeded. Handle an expected domain condition narrowly, and re-raise unexpected errors after approved diagnostic capture. Do not place passwords or full customer payloads in error text. A PL/SQL exception does not by itself define whether the application should retry the whole transaction, compensate an external effect or show a validation message. Publish those decisions at the interface boundary. Review autonomous transactions separately: they have independent commit behavior and should not be introduced merely to make error logs survive without understanding the consistency consequences.

Common errors and fixes

ORA-01403 / NO_DATA_FOUND

Why it happens: SELECT INTO found no qualifying row.
How to fix it: Handle the intended absence explicitly or repair the lookup condition.
Prevent it next time: Document whether absence is a domain outcome or corrupted expectation.

ORA-01422 / TOO_MANY_ROWS

Why it happens: SELECT INTO found more than its one-row contract permits.
How to fix it: Repair uniqueness or use an intended multirow cursor interface.
Prevent it next time: Back singleton lookups with the right key constraint.

A failed operation is reported as success

Why it happens: A broad exception handler swallowed the underlying failure.
How to fix it: Re-raise unexpected errors and map only reviewed domain outcomes.
Prevent it next time: Review exception handlers alongside transaction and retry contracts.

Check your understanding

1. Why does the reservation block inspect SQL%ROWCOUNT immediately?

Show worked solution

It needs the result of that UPDATE, not a later SQL statement. Exactly one row is the success contract; zero means the intended state transition did not occur. The caller still must commit the broader unit or roll it back. A block completing without a syntax error is insufficient evidence of a reserved slot.

2. Should a general-purpose booking helper issue COMMIT before returning?