Exception Handling

When something goes wrong at runtime — a query returns no rows, a division by zero occurs, or a constraint is violated — Oracle raises an exception. The EXCEPTION block in a PL/SQL block lets you catch and handle these errors gracefully.

The EXCEPTION Block

BEGIN
  -- risky code
EXCEPTION
  WHEN exception_name THEN
    -- handle it
  WHEN OTHERS THEN
    -- catch anything else
END;
/

Execution jumps to the EXCEPTION block as soon as an exception is raised. After the handler completes, control does not return to the BEGIN block — the block ends.

Predefined Exceptions

Oracle provides named exceptions for common error conditions:

Exception	ORA Code	Raised when
NO_DATA_FOUND	ORA-01403	SELECT INTO returns zero rows
TOO_MANY_ROWS	ORA-01422	SELECT INTO returns more than one row
ZERO_DIVIDE	ORA-01476	Division by zero
DUP_VAL_ON_INDEX	ORA-00001	Unique constraint violated
VALUE_ERROR	ORA-06502	Conversion or truncation error
INVALID_NUMBER	ORA-01722	Failed string-to-number conversion in SQL
CURSOR_ALREADY_OPEN	ORA-06511	Opening an already-open cursor

Handling Predefined Exceptions

DECLARE
  v_name employees.first_name%TYPE;
BEGIN
  SELECT first_name INTO v_name FROM employees WHERE id = 9999;
  DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Query returned multiple rows.');
END;
/

WHEN OTHERS

Catches any exception not matched by a previous WHEN clause. Always put it last:

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Not found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;

SQLCODE and SQLERRM

Within an exception handler, these built-in functions tell you what went wrong:

• SQLCODE — the Oracle error number (negative for errors, e.g. -1403)
• SQLERRM — the error message text

WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error ' || SQLCODE || ': ' || SQLERRM);

User-Defined Exceptions

Declare your own exception, then RAISE it when a business rule is violated:

DECLARE
  e_salary_too_low EXCEPTION;
  v_salary NUMBER := 5000;
BEGIN
  IF v_salary < 10000 THEN
    RAISE e_salary_too_low;
  END IF;
EXCEPTION
  WHEN e_salary_too_low THEN
    DBMS_OUTPUT.PUT_LINE('Salary is below the minimum threshold.');
END;
/

RAISE_APPLICATION_ERROR

For meaningful error messages from stored procedures and functions, use RAISE_APPLICATION_ERROR. Error numbers must be between -20001 and -20999:

IF v_salary < 0 THEN
  RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative.');
END IF;