PL/SQL Exception Handling: How to Write Bulletproof Code

Every database application will encounter unexpected situations — a record that does not exist, a constraint violation, a network timeout, a value that cannot be converted. How your PL/SQL code handles these situations determines whether your application is robust or fragile.

In this guide, we cover the full Oracle PL/SQL exception handling model: predefined exceptions, user-defined exceptions, SQLCODE and SQLERRM, RAISE_APPLICATION_ERROR, and the patterns that make exception handling genuinely useful in production code.

The Basic Exception Handling Structure

Every PL/SQL block can have an optional EXCEPTION section that catches and handles runtime errors:

BEGIN
  -- Normal executable code
  NULL;
  
EXCEPTION
  WHEN exception_name THEN
    -- Handle the exception
    NULL;
  WHEN OTHERS THEN
    -- Catch anything not handled above
    NULL;
END;

When an exception is raised (either by Oracle automatically or by your code using RAISE), execution jumps immediately to the EXCEPTION section. The matching WHEN clause runs. If no WHEN clause matches, the exception propagates up to the calling block.

Oracle’s Predefined Exceptions

Oracle provides named exceptions for the most common error situations. You can reference these directly in WHEN clauses:

Exception Name	ORA Error	When It Occurs
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 violation
VALUE_ERROR	ORA-06502	Type mismatch or value too large for variable
INVALID_NUMBER	ORA-01722	Cannot convert string to number
CURSOR_ALREADY_OPEN	ORA-06511	OPEN called on an already-open cursor
INVALID_CURSOR	ORA-01001	Invalid cursor operation
TIMEOUT_ON_RESOURCE	ORA-00051	Wait timeout for a resource

DECLARE
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary
  INTO   v_salary
  FROM   employees
  WHERE  employee_id = 9999; -- Does not exist
  
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found. Check the ID and try again.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Query returned multiple rows. Refine your filter.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;

SQLCODE and SQLERRM

Inside any exception handler, two functions give you information about the error:

• SQLCODE — Returns the Oracle error number (negative for Oracle errors, +1 for user-defined exceptions without an assigned number, 100 for NO_DATA_FOUND)
• SQLERRM — Returns the full error message text, including the ORA- error code

EXCEPTION
  WHEN OTHERS THEN
    -- Log the full error details
    INSERT INTO error_log (
      error_date, error_code, error_message, program_unit
    ) VALUES (
      SYSDATE, SQLCODE, SUBSTR(SQLERRM, 1, 500), 'PROCESS_ORDER'
    );
    COMMIT;
    RAISE; -- Re-raise the original exception
END;

Always SUBSTR SQLERRM to a safe length (500 characters is plenty) before inserting it into a VARCHAR2 column — the full message can be longer.

User-Defined Exceptions

You can define your own named exceptions for business logic errors:

DECLARE
  -- Define a user exception
  e_insufficient_credit EXCEPTION;
  v_balance             NUMBER;
  v_order_amount        NUMBER := 5000;
BEGIN
  SELECT credit_balance
  INTO   v_balance
  FROM   customer_accounts
  WHERE  customer_id = 101;
  
  -- Check business rule
  IF v_balance < v_order_amount THEN
    RAISE e_insufficient_credit; -- Raise our custom exception
  END IF;
  
  -- Process the order
  DBMS_OUTPUT.PUT_LINE('Order processed successfully.');
  
EXCEPTION
  WHEN e_insufficient_credit THEN
    DBMS_OUTPUT.PUT_LINE(
      'Order rejected: insufficient credit balance. ' ||
      'Balance: ' || v_balance || ', Required: ' || v_order_amount
    );
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Customer account not found.');
END;

RAISE_APPLICATION_ERROR: Meaningful Error Messages

RAISE_APPLICATION_ERROR lets you raise exceptions with a custom error number (between -20000 and -20999) and a descriptive message. This is the standard way to communicate business rule violations from PL/SQL to calling applications:

CREATE OR REPLACE PROCEDURE process_refund (
  p_order_id  IN NUMBER,
  p_amount    IN NUMBER
) IS
  v_order_status VARCHAR2(20);
  v_order_amount NUMBER;
BEGIN
  SELECT status, total_amount
  INTO   v_order_status, v_order_amount
  FROM   orders
  WHERE  order_id = p_order_id;
  
  -- Business validations
  IF v_order_status = 'CANCELLED' THEN
    RAISE_APPLICATION_ERROR(-20001, 
      'Refund denied: Order ' || p_order_id || ' is already cancelled.');
  END IF;
  
  IF p_amount > v_order_amount THEN
    RAISE_APPLICATION_ERROR(-20002, 
      'Refund amount (' || p_amount || ') exceeds original order total (' || 
      v_order_amount || ').');
  END IF;
  
  -- Process refund
  UPDATE orders SET refund_amount = p_amount WHERE order_id = p_order_id;
  COMMIT;
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20003, 'Order ID ' || p_order_id || ' not found.');
END;

The calling application receives the ORA-20001 (or -20002, -20003) error with your exact message, making it easy to display meaningful feedback to users.

Exception Propagation

Understanding how exceptions propagate is critical. If an exception is not handled in the current block, it propagates to the enclosing block:

BEGIN -- Outer block
  BEGIN -- Inner block
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Inner: Employee not found, continuing...');
      -- Exception is handled here. Outer block continues normally.
  END;
  
  DBMS_OUTPUT.PUT_LINE('Outer block: continuing after inner block handled exception.');
  
EXCEPTION
  WHEN OTHERS THEN
    -- Only reaches here if inner block does NOT handle the exception
    DBMS_OUTPUT.PUT_LINE('Outer: Unhandled exception: ' || SQLERRM);
END;

The RAISE Statement: Re-Raising Exceptions

Inside an exception handler, a bare RAISE (without an exception name) re-raises the current exception. This is the right pattern when you want to log an error but still let it propagate:

EXCEPTION
  WHEN OTHERS THEN
    -- Log the error
    log_error(SQLCODE, SQLERRM, 'CALCULATE_BONUS');
    -- Re-raise: let the caller know something went wrong
    RAISE;
END;

Associating Error Numbers with Named Exceptions

Some Oracle errors do not have predefined names but occur frequently enough that you want to catch them by name. Use EXCEPTION_INIT pragma:

DECLARE
  e_deadlock      EXCEPTION;
  e_table_missing EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_deadlock,      -60);
  PRAGMA EXCEPTION_INIT(e_table_missing, -942);
BEGIN
  -- Your code here
  NULL;
EXCEPTION
  WHEN e_deadlock THEN
    DBMS_OUTPUT.PUT_LINE('Deadlock detected. Retry the transaction.');
  WHEN e_table_missing THEN
    DBMS_OUTPUT.PUT_LINE('Required table does not exist.');
END;

Exception Handling Best Practices

• Always handle NO_DATA_FOUND when using SELECT INTO — Unhandled NO_DATA_FOUND in production is a very common bug
• Never silently swallow exceptions — An empty WHEN OTHERS THEN NULL hides bugs and makes diagnosis impossible
• Use RAISE_APPLICATION_ERROR for business rule violations — It gives calling code and users meaningful feedback
• Log before you re-raise — Capture the context (SQLCODE, SQLERRM, program unit name, relevant IDs) in a log table before re-raising
• Avoid WHEN OTHERS as the only handler — Be specific about the exceptions you expect. WHEN OTHERS is for truly unexpected scenarios
• Never COMMIT inside a WHEN OTHERS handler — If an error occurred, committing partial work can corrupt data

A Production-Ready Error Logging Pattern

CREATE OR REPLACE PROCEDURE log_error (
  p_error_code    IN NUMBER,
  p_error_message IN VARCHAR2,
  p_program_unit  IN VARCHAR2,
  p_context       IN VARCHAR2 DEFAULT NULL
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO application_error_log (
    log_id, log_date, error_code, error_message, 
    program_unit, context_info, db_user, session_id
  ) VALUES (
    error_log_seq.NEXTVAL,
    SYSTIMESTAMP,
    p_error_code,
    SUBSTR(p_error_message, 1, 2000),
    p_program_unit,
    p_context,
    USER,
    USERENV('SESSIONID')
  );
  COMMIT; -- Autonomous transaction commits independently
END log_error;

The PRAGMA AUTONOMOUS_TRANSACTION is critical here — it lets the error log commit even if the calling transaction is rolled back. This ensures you never lose error information.

Conclusion

Solid exception handling is what separates prototype code from production-ready code. The difference between a system that crashes with "ORA-01403" and one that logs the error, provides a clear message, and continues gracefully comes down entirely to thoughtful exception handling.

Use predefined exceptions where they exist, define custom exceptions for business rules, always log before re-raising, and treat WHEN OTHERS as a last resort rather than a first line of defense. Your future self — and the developers who maintain your code — will thank you.