Oracle PL/SQL Exception Handling

An Exception is a PL/SQL runtime error condition triggered during code execution. When an exception is raised, normal execution of the current PL/SQL block halts immediately, and control transfers to the EXCEPTION section. If an appropriate exception handler is present, final corrective actions are executed before the block exits; otherwise, the unhandled error propagates to the host environment.

Exceptions in PL/SQL are classified into three distinct categories:

Exception Type	Description	Declaration Requirement	How Raised
Predefined Oracle Server	Common system errors (~20 named standard errors like NO_DATA_FOUND).	Automatically declared by Oracle.	Implicitly by the Oracle database engine.
Non-Predefined Oracle Server	Standard Oracle system errors that do not have pre-assigned PL/SQL names.	Declared in the DECLARE section and bound using PRAGMA EXCEPTION_INIT.	Implicitly by the Oracle database engine.
User-Defined	Custom business logic violations defined by application developers.	Declared in the DECLARE section.	Explicitly using RAISE or RAISE_APPLICATION_ERROR.

IMPORTANT:

• Only one exception handler can be executed per block execution.
• A block can contain multiple exception handlers, but it can have only one WHEN OTHERS clause.
• The WHEN OTHERS handler must be the last exception handler listed in the EXCEPTION section.
• Exception handlers cannot appear inside assignment statements or standard SQL statements.

1. Predefined Oracle Server Exceptions

Oracle pre-defines names for common runtime errors in the STANDARD package. You do not need to declare these in your code block; reference them directly by name.

• Common Predefined Exceptions:
  • NO_DATA_FOUND (ORA-01403): A SELECT INTO statement returned zero rows.
  • TOO_MANY_ROWS (ORA-01422): A SELECT INTO statement returned more than one row.
  • INVALID_CURSOR (ORA-01001): Illegal cursor operation (e.g., closing an unopened cursor).
  • ZERO_DIVIDE (ORA-01476): Attempted division by zero.
  • VALUE_ERROR (ORA-06502): Truncation, arithmetic conversion, or constraint enforcement error on local variables.
  • DUP_VAL_ON_INDEX (ORA-00001): Attempted duplicate insertion into a unique index column.

Code Example:

SQL

DECLARE
  v_ename employees.last_name%TYPE;
BEGIN
  SELECT last_name INTO v_ename
  FROM employees
  WHERE department_id = 10;

  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_ename);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Error: No employee found for the specified department.');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Error: Multiple employees found. Single-row fetch expected.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

2. Non-Predefined Oracle Server Exceptions

Non-predefined exceptions handle standard Oracle server errors that lack explicit built-in names (e.g., ORA-01400: cannot insert NULL).

Implementation Steps:

1. Declare the exception name in the DECLARE block.
2. Associate the exception name with the specific ORA- error number using PRAGMA EXCEPTION_INIT.
3. Reference and handle the named exception in the EXCEPTION section.

NOTE: PRAGMA is a compiler directive (pseudo-instruction) evaluated at compile time rather than runtime. It instructs the PL/SQL compiler to bind the user-declared exception name directly to the specific Oracle server error code.

Code Example:

DECLARE
  e_null_insert EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_null_insert, -01400); -- ORA-01400: cannot insert NULL
BEGIN
  INSERT INTO departments (department_id, department_name)
  VALUES (280, NULL);

EXCEPTION
  WHEN e_null_insert THEN
    DBMS_OUTPUT.PUT_LINE('Insert operation failed: Mandatory column cannot be NULL.');
    DBMS_OUTPUT.PUT_LINE('System Message: ' || SQLERRM);
END;
/

3. Built-In Error Handling Functions

Oracle provides two key functions inside exception handlers to extract diagnostic data:

Function	Output Type	Description & Behavior
SQLCODE	NUMBER	Returns numeric error code.

• 0: Success (No error)

• 1: User-defined exception

• +100: NO_DATA_FOUND (ANSI standard)

• Negative integer: Other Oracle error codes. |
| SQLERRM | VARCHAR2 | Returns error message string corresponding to the SQLCODE. Default limit is 512 bytes. |

IMPORTANT: SQLCODE and SQLERRM cannot be used directly inside SQL statements (e.g., INSERT INTO log_table VALUES (SQLCODE, SQLERRM); will fail). You must first assign them to local variables or use them via standard PL/SQL wrapper statements.

Error Logger Table Setup & Code Example:

-- Target table structure for audit/logging
CREATE TABLE error_log (
  log_id       NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  error_code   NUMBER,
  error_msg    VARCHAR2(4000),
  logged_user  VARCHAR2(100),
  logged_date  DATE DEFAULT SYSDATE
);

DECLARE
  v_err_code NUMBER;
  v_err_msg  VARCHAR2(500);
BEGIN
  -- Intentional zero-divide error
  DBMS_OUTPUT.PUT_LINE(10 / 0);
EXCEPTION
  WHEN OTHERS THEN
    v_err_code := SQLCODE;
    v_err_msg  := SUBSTR(SQLERRM, 1, 500);

    INSERT INTO error_log (error_code, error_msg, logged_user)
    VALUES (v_err_code, v_err_msg, USER);
    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Error trapped and logged to table successfully.');
END;
/

4. User-Defined Exceptions

When standard system errors do not reflect custom business conditions (e.g., enforcing logic restrictions like "Division by 1 is forbidden"), developers must define custom exceptions.

Comparison of User-Defined Exception Methods:

Characteristic	RAISE Statement	RAISE_APPLICATION_ERROR
Mechanism	Raises error using a declared exception variable name.	Built-in procedure raising custom error code & message interactively.
Allowed Error Codes	Standard execution flow handling without explicit numbers.	Developer-assigned range: -20000 to -20999.
Call Locations	Executable and Exception sections.	Executable and Exception sections.
Handling Requirement	Must be caught inside PL/SQL EXCEPTION section; otherwise unhandled.	Returns execution error directly to host application or call stack.

Syntax for RAISE_APPLICATION_ERROR:

RAISE_APPLICATION_ERROR(error_number, message [, {TRUE | FALSE}]);

• error_number: An integer between 20000 and 20999.
• message: A character string up to 2,048 bytes.
• TRUE | FALSE (Optional): If TRUE, the error is added to the stack of previous errors. If FALSE (default), it replaces all previous errors in the stack.

User-Defined Example 1: Standard RAISE Statement

DECLARE
  v_num1        NUMBER := &enter_num1;
  v_num2        NUMBER := &enter_num2;
  v_result      NUMBER;
  e_divide_by_one EXCEPTION;
BEGIN
  IF v_num2 = 1 THEN
    RAISE e_divide_by_one;
  END IF;

  v_result := v_num1 / v_num2;
  DBMS_OUTPUT.PUT_LINE('Calculation Result: ' || v_result);

EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero.');
  WHEN e_divide_by_one THEN
    DBMS_OUTPUT.PUT_LINE('Business Rule Violation: Division by 1 is not permitted.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error (' || SQLCODE || '): ' || SQLERRM);
END;
/

User-Defined Example 2: RAISE_APPLICATION_ERROR Procedure

DECLARE
  v_dept_id NUMBER := &dept_id;
  v_dept_name VARCHAR2(50) := '&dept_name';
BEGIN
  UPDATE departments
  SET department_name = v_dept_name
  WHERE department_id = v_dept_id;

  IF SQL%NOTFOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid Department ID: ' || v_dept_id || '. Update failed.');
  END IF;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- Re-raising exception back to calling client environment
    RAISE;
END;
/

5. Enterprise Architecture: Package-Based Standard Exception Handling

To avoid repeating exception logic and code duplication across enterprise applications, centralized packages are created to declare global application exceptions and log errors cleanly.

Package Specification (Global Definitions)

CREATE OR REPLACE PACKAGE app_error_pkg IS
  -- Custom Exception Declarations
  e_fk_violation EXCEPTION;
  e_parent_not_found EXCEPTION;

  -- Bind exceptions to standard ORA- error numbers
  PRAGMA EXCEPTION_INIT(e_fk_violation, -2292);
  PRAGMA EXCEPTION_INIT(e_parent_not_found, -2270);

  -- Standardized Procedure for Logging
  PROCEDURE log_error(
    p_proc_name IN VARCHAR2,
    p_err_code  IN NUMBER,
    p_err_msg   IN VARCHAR2
  );
END app_error_pkg;
/

Package Body (Autonomous Error Logging)

CREATE OR REPLACE PACKAGE BODY app_error_pkg IS
  PROCEDURE log_error(
    p_proc_name IN VARCHAR2,
    p_err_code  IN NUMBER,
    p_err_msg   IN VARCHAR2
  ) IS
    -- Ensures error log commits without affecting caller transaction state
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO error_log (error_code, error_msg, logged_user, logged_date)
    VALUES (p_err_code, '[' || p_proc_name || '] ' || p_err_msg, USER, SYSDATE);
    COMMIT;
  END log_error;
END app_error_pkg;
/

Executing Package-Based Exception Handler

BEGIN
  DELETE FROM departments WHERE department_id = 10;
EXCEPTION
  WHEN app_error_pkg.e_fk_violation THEN
    app_error_pkg.log_error('DEPT_DELETE_PROC', SQLCODE, SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Cannot delete department: Active child employee records exist.');
  WHEN OTHERS THEN
    app_error_pkg.log_error('DEPT_DELETE_PROC', SQLCODE, SQLERRM);
    RAISE;
END;
/

Interview Essentials: Critical Questions

1. Exception Propagation Rules

• Enclosed Blocks: If an exception is raised in an inner block and no handler matches, control moves immediately to the exception section of the enclosing outer block.
• Declarative Section Errors: If an exception occurs in a DECLARE section (e.g., initial value overflow), it cannot be caught by the exception handler of that same block. It propagates immediately to the enclosing parent block.

2. Exact Line Tracing: DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

• Standard SQLERRM reports what the error is, but not where it occurred. Senior developers use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE inside exception blocks to output the exact PL/SQL line number where the error was generated.

3. Top Interview Technical Questions & Answers

• Q1: Can a single exception handler catch multiple exceptions?
  • Answer: Yes. Use OR inside the handler: WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN ....
• Q2: What is the difference between RAISE; and RAISE_APPLICATION_ERROR inside an exception block?
  • Answer: RAISE; re-raises the exact original exception up the call stack without altering context. RAISE_APPLICATION_ERROR terminates block execution with a customized error number (20000 to 20999) and message back to the application client.
• Q3: Why should error logging procedures use PRAGMA AUTONOMOUS_TRANSACTION?
  • Answer: If a main transaction fails and must perform a ROLLBACK, standard insert operations into an error log table would be rolled back as well. PRAGMA AUTONOMOUS_TRANSACTION allows the log procedure to commit its insert independently of the main transaction's status.

Practice Exercises

1. Exercise 1 (Non-Predefined Exception): Write an anonymous block that attempts to update an employee salary to NULL. Define a non-predefined exception for ORA-01407 ("cannot update to NULL") and print a clean error message.
2. Exercise 2 (Custom Validation & Autonomous Logging): Write a procedure TRANSFER_FUNDS that takes p_from_acc, p_to_acc, and p_amount. If p_amount > 50000, raise RAISE_APPLICATION_ERROR(-20050, 'Transfer limit exceeded') and verify that the attempt gets logged to an error_log table using autonomous transactions.