Handling errors gracefully in PL/SQL

Every production-quality PL/SQL program needs proper error handling. Oracle provides a rich exception handling framework that lets you catch errors, log them, and recover gracefully. Knowing the difference between predefined exceptions, user-defined exceptions, and the OTHERS handler is essential.

Recipe #1 - Catching predefined exceptions

Oracle provides named exceptions for common errors. The most frequently used ones are NO_DATA_FOUND and TOO_MANY_ROWS:

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT ename INTO v_name
  FROM emp
  WHERE empno = 9999;

  DBMS_OUTPUT.put_line('Employee: ' || v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('No employee found with that ID.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.put_line('Multiple employees found — query is ambiguous.');
END;

Recipe #2 - User-defined exceptions

You can declare your own exceptions for business logic validation:

DECLARE
  e_salary_too_high EXCEPTION;
  v_salary NUMBER;
BEGIN
  SELECT sal INTO v_salary FROM emp WHERE empno = 7839;

  IF v_salary > 50000 THEN
    RAISE e_salary_too_high;
  END IF;
EXCEPTION
  WHEN e_salary_too_high THEN
    DBMS_OUTPUT.put_line('Salary exceeds the allowed maximum.');
END;

Recipe #3 - Using SQLCODE and SQLERRM

When you catch exceptions with the OTHERS handler, use SQLCODE and SQLERRM to get details about what went wrong:

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE nonexistent_table';
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error code: ' || SQLCODE);
    DBMS_OUTPUT.put_line('Error message: ' || SQLERRM);
END;

Recipe #4 - RAISE_APPLICATION_ERROR for custom error codes

Use RAISE_APPLICATION_ERROR to send meaningful error messages back to the calling application. Error numbers must be between -20000 and -20999:

CREATE OR REPLACE PROCEDURE withdraw(
  p_account_id NUMBER,
  p_amount     NUMBER
) AS
  v_balance NUMBER;
BEGIN
  SELECT balance INTO v_balance
  FROM accounts
  WHERE account_id = p_account_id;

  IF p_amount > v_balance THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Insufficient funds. Balance: ' || v_balance
      || ', requested: ' || p_amount);
  END IF;

  UPDATE accounts
  SET balance = balance - p_amount
  WHERE account_id = p_account_id;
END;