Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 38 ยท Exceptions and Transactions

PL/SQL Exception Handling Basics in Oracle

Handle runtime errors in PL/SQL: the EXCEPTION section, predefined exceptions like NO_DATA_FOUND, WHEN OTHERS, SQLCODE and SQLERRM, propagation and nested blocks.

Things go wrong at run time: a query finds no row, a division hits zero, a value is too big for its column, two sessions fight over a lock. In PL/SQL, such a problem raises an exception. If you do nothing, the exception travels up to the caller and, at the top, the client sees an error and the failed statement is rolled back. With an exception handler you decide what happens instead: recover, log, translate the error or let it go on knowingly.

Oracle cannot run on this site. PL/SQL blocks are read-only here and show output only where it is certain. The runnable examples are Python and SQLite stand-ins for the same control flow.

The EXCEPTION section

A block has an optional EXCEPTION section after its executable part:

SQL
DECLARE
  v_name employees.first_name%TYPE;
BEGIN
  SELECT first_name INTO v_name FROM employees WHERE employee_id = 9999;
  DBMS_OUTPUT.PUT_LINE('Found ' || v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such employee');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('More than one employee matched');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

When an exception is raised, normal execution stops at that line and control jumps to the first WHEN branch that matches. After the handler finishes, the block ends. Execution does not resume after the statement that failed. If no handler matches, the exception propagates to the enclosing block, then outward to the caller.

The output of this block, for a table that has no employee 9999, is No such employee.

Predefined exceptions

Oracle names the most common errors for you:

Exception When ORA code
NO_DATA_FOUND SELECT INTO finds no row ORA-01403
TOO_MANY_ROWS SELECT INTO finds more than one row ORA-01422
ZERO_DIVIDE division by zero ORA-01476
DUP_VAL_ON_INDEX duplicate value in a unique index ORA-00001
INVALID_NUMBER a string cannot convert to a number in SQL ORA-01722
VALUE_ERROR conversion, size or truncation error in PL/SQL ORA-06502
CURSOR_ALREADY_OPEN opening an open cursor ORA-06511
INVALID_CURSOR illegal cursor operation ORA-01001
LOGIN_DENIED, TIMEOUT_ON_RESOURCE connection and lock-wait problems ORA-01017, ORA-00051

Exceptions that Oracle does not name can still be caught under WHEN OTHERS or by giving them a name yourself (see user-defined exceptions).

WHEN OTHERS, SQLCODE and SQLERRM

WHEN OTHERS catches everything not matched before it, so it must be the last handler. Inside it two functions describe the current error:

  • SQLCODE is the error number: negative for most Oracle errors (-1476 for a divide by zero), 100 for NO_DATA_FOUND, and 1 for user-defined ones.
  • SQLERRM is the message text, such as ORA-01476: divisor is equal to zero.
SQL
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' - ' || SQLERRM);

For a divide by zero, that prints -1476 - ORA-01476: divisor is equal to zero.

An empty WHEN OTHERS THEN NULL; swallows every error and leaves the program continuing as if nothing happened. That hides bugs and corrupt data. A WHEN OTHERS handler should log the error and then re-raise it with RAISE;, unless you have a specific reason to continue.

For a full stack trace use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, which gives the line numbers where the error began. SQLERRM alone does not.

Propagation through nested blocks

Blocks can contain blocks, and each can have its own handler. An exception raised in an inner block is looked for there first, then in the enclosing block, and so on outward.

SQL
BEGIN
  BEGIN
    v_result := 10 / 0;            -- ZERO_DIVIDE raised here
  EXCEPTION
    WHEN NO_DATA_FOUND THEN        -- does not match
      NULL;
  END;
  DBMS_OUTPUT.PUT_LINE('never printed');
EXCEPTION
  WHEN ZERO_DIVIDE THEN            -- caught by the outer block
    DBMS_OUTPUT.PUT_LINE('Outer caught it');
END;
/

A common technique is a sub-block per risky step so one failure does not abort the whole loop:

SQL
FOR r IN (SELECT employee_id FROM employees) LOOP
  BEGIN
    process_employee(r.employee_id);
  EXCEPTION
    WHEN OTHERS THEN
      log_error(r.employee_id, SQLERRM);   -- keep going with the next employee
  END;
END LOOP;

Exceptions raised in the declaration section (for instance v_x NUMBER(3) := 1000;) or inside an exception handler itself are not caught by that block's own handlers; they propagate outward.

The Python stand-in below reproduces the matching and propagation rules with try/except:

Python Handler matching and propagation (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.

Reading data safely: NO_DATA_FOUND versus aggregate

SELECT INTO raises NO_DATA_FOUND when there is no row, but an aggregate query such as SELECT MAX(salary) INTO v FROM employees WHERE ... always returns one row (with NULL if nothing matched), so it never raises. A frequent bug is handling NO_DATA_FOUND around an aggregate and wondering why the handler never fires. Check the result for NULL instead.

SQLite behaves the same way, which this stand-in shows:

SQL Aggregates always return a row (SQLite stand-in)

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

Re-raising and transaction effects

Inside a handler, a bare RAISE; re-raises the current exception with its original error code and message, so the caller still sees it. RAISE_APPLICATION_ERROR(-20001, 'message') raises a new, user-defined one.

Oracle also protects data integrity automatically: if a PL/SQL statement fails, Oracle undoes that statement. If the exception goes unhandled to the client, the whole top-level call is rolled back to its start (statement-level atomicity). But if you catch an error inside your block and keep going, earlier successful DML in the transaction stays. Decide deliberately whether to ROLLBACK in the handler; see commit, rollback and savepoints.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens after an exception handler finishes?

    Oracle does not resume at the failed statement. The block that handled the exception completes.
  2. 2. Where should WHEN OTHERS appear in the EXCEPTION section?

    It matches every exception, so any handler after it could never be reached. Oracle requires it to be last.
  3. 3. SELECT MAX(salary) INTO v FROM employees WHERE employee_id = 9999 finds no employees. What is the result?

    An aggregate without GROUP BY always returns exactly one row, holding NULL when nothing matched.
  4. 4. Why is WHEN OTHERS THEN NULL considered dangerous?

    Catch what you can handle, log the rest, and re-raise.

Summary

  • An error raises an exception; the EXCEPTION section chooses a handler, and the block ends after it. Unhandled exceptions propagate outward.
  • Use predefined names (NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, DUP_VAL_ON_INDEX) for expected cases and a final WHEN OTHERS that logs and re-raises.
  • SQLCODE and SQLERRM describe the current error; DBMS_UTILITY.FORMAT_ERROR_BACKTRACE gives line numbers.
  • Wrap each risky step in its own sub-block to continue a loop after a failure.

Next: User-Defined Exceptions and Error Logging