PL/SQL Exception Handling — Silent Data Loss, NO_DATA_FOUND

PL/SQL Exception Handling is a fundamental concept in Database development. In the world of Oracle programming, an exception is an error condition that occurs during the execution of a block. Effective handling ensures that your application remains stable even when the database encounters unexpected data or system issues.

In this guide we'll break down exactly what PL/SQL Exception Handling is, why it was designed to separate error logic from business logic, and how to use it correctly in real projects.

By the end you'll have both the conceptual understanding and practical code examples to use PL/SQL Exception Handling with confidence.

What Is PL/SQL Exception Handling and Why Does It Exist?

PL/SQL Exception Handling is a core feature of PL/SQL. It was designed to solve a specific problem: unhandled errors causing transaction failure and messy stack traces for the end-user. It exists to provide a centralized 'EXCEPTION' block at the end of your code, keeping your main execution path clean. There are three categories: Predefined (like NO_DATA_FOUND), Non-predefined (standard Oracle errors mapped to names), and User-defined (business rule violations). This architecture allows for graceful recovery, customized error logging, and consistent transaction management via ROLLBACK.

-- io.thecodeforge: Standard Exception Handling Pattern
DECLARE
    v_project_name forge_projects.name%TYPE;
    e_invalid_status EXCEPTION; -- User-defined exception
BEGIN
    -- Standard query that might fail
    SELECT name INTO v_project_name 
    FROM forge_projects 
    WHERE project_id = 999; 

    -- Business logic check
    IF v_project_name IS NULL THEN
        RAISE e_invalid_status;
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Forge Error: Project record not found.');
    WHEN e_invalid_status THEN
        DBMS_OUTPUT.PUT_LINE('Forge Error: Project status is corrupted.');
    WHEN OTHERS THEN
        -- Catch-all for unexpected issues
        DBMS_OUTPUT.PUT_LINE('Unexpected Error Code: ' || SQLCODE || ' - ' || SQLERRM);
        RAISE; -- Re-propagate the error after logging
END;

Types of Exceptions: Predefined, Non-Predefined, and User-Defined

Oracle provides a set of predefined exceptions like NO_DATA_FOUND (ORA-01403), TOO_MANY_ROWS (ORA-01422), DUP_VAL_ON_INDEX (ORA-00001), and others. These are automatically raised when the corresponding Oracle error occurs. Non-predefined exceptions cover standard Oracle errors that don't have a built-in name — you map them using PRAGMA EXCEPTION_INIT. User-defined exceptions are declared with the EXCEPTION keyword and raised explicitly with RAISE or RAISE_APPLICATION_ERROR. The choice depends on the error's source: predefined for common Oracle errors, non-predefined for specific ORA codes you want a name for, user-defined for your application's business rules.

-- io.thecodeforge: Mapping a non-predefined Oracle error
DECLARE
    e_fk_violation EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_fk_violation, -2292);  -- ORA-02292: child record found
BEGIN
    DELETE FROM forge_projects WHERE project_id = 100;
EXCEPTION
    WHEN e_fk_violation THEN
        DBMS_OUTPUT.PUT_LINE('Cannot delete: foreign key constraint violated');
        -- Optionally re-raise with custom message
        RAISE_APPLICATION_ERROR(-20002, 'Cannot delete active project');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected: ' || SQLERRM);
        RAISE;
END;

Exception Propagation and Scope

Exceptions propagate from inner blocks to outer blocks until caught or until they reach the outermost block and become unhandled, causing the program to terminate. If an exception is raised in the declarative section (e.g., a variable initialisation fails), it cannot be caught by the same block's EXCEPTION section — it propagates immediately to the enclosing block. Understanding scope is critical: each BEGIN-END block has its own exception section. An exception raised in an inner block that is not handled there will propagate to the outer block's EXCEPTION section. You can use nested blocks to isolate errors and continue processing while still maintaining transactional integrity.

-- io.thecodeforge: Nested block isolation
DECLARE
    v_total NUMBER := 0;
BEGIN
    FOR rec IN (SELECT id FROM forge_tasks WHERE status = 'ACTIVE') LOOP
        BEGIN
            -- Inner block isolates this row's processing
            UPDATE forge_tasks SET processed_date = SYSDATE WHERE id = rec.id;
            v_total := v_total + 1;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Skipping task ' || rec.id || ' due to: ' || SQLERRM);
                -- Continue with next record; transaction not rolled back
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Processed ' || v_total || ' tasks successfully.');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;

RAISE vs RAISE_APPLICATION_ERROR: When to Use Each

RAISE is used to re-raise the current exception or explicitly raise a user-defined exception declared with EXCEPTION. It preserves the original error stack. RAISE_APPLICATION_ERROR is a built-in procedure that raises a user-defined Oracle error with a custom message and optional flag to keep the error stack. Use RAISE_APPLICATION_ERROR when you want to raise a new error with a specific code (between -20000 and -20999) and message, especially when the caller expects a numeric error code. For simple business rule violations, a user-defined exception with RAISE is cleaner. For errors that need to be caught by external applications (like Java via JDBC), RAISE_APPLICATION_ERROR is better because it provides a standard Oracle error.

-- io.thecodeforge: RAISE vs RAISE_APPLICATION_ERROR
DECLARE
    e_insufficient_funds EXCEPTION;
    v_balance NUMBER := 50;
    v_withdraw NUMBER := 100;
BEGIN
    IF v_withdraw > v_balance THEN
        -- Option 1: user-defined exception + RAISE
        RAISE e_insufficient_funds;
    END IF;
EXCEPTION
    WHEN e_insufficient_funds THEN
        -- Option 2: re-raise with custom ORA error
        RAISE_APPLICATION_ERROR(-20101, 'Insufficient funds: balance ' || v_balance || ' < ' || v_withdraw);
END;

Common Mistakes and How to Avoid Them

When learning PL/SQL Exception Handling, most developers hit the same set of gotchas. The most dangerous is the 'Silent Failure'—using WHEN OTHERS THEN NULL; which swallows the error and makes debugging impossible. Another is failing to ROLLBACK a failed transaction, leaving the database in an inconsistent state.

Knowing these in advance saves hours of debugging production outages and prevents data corruption.

-- io.thecodeforge: Handling exceptions in nested blocks
BEGIN
    -- Outer Block
    BEGIN
        -- Inner Block: isolate failures to keep the outer process alive
        INSERT INTO forge_logs (msg) VALUES ('Starting Task');
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Log failed, but continuing main task.');
    END;

    -- Main business logic continues here
    UPDATE forge_stats SET count = count + 1;
END;

Best Practices for Production-Ready Exception Handling

Beyond the basics, robust exception handling in production requires logging, transaction management, and consistent propagation. Create a central error logging procedure that captures SQLCODE, SQLERRM, backtrace (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE), and timestamp. Use autonomous transactions for error logging so that log inserts survive a rollback. Always re-raise exceptions after logging unless the error is truly recoverable. For batch processing, use SAVEPOINT and ROLLBACK TO SAVEPOINT to isolate failed rows. Handle COM (Communication) and TIMEOUT_ON_RESOURCE separately from application errors.

-- io.thecodeforge: Central error logging procedure
CREATE OR REPLACE PACKAGE io_thecodeforge.error_log_pkg AS
    PROCEDURE log_error(
        p_procedure_name VARCHAR2,
        p_sqlcode        NUMBER,
        p_sqlerrm        VARCHAR2,
        p_backtrace      VARCHAR2
    );
END;
/
CREATE OR REPLACE PACKAGE BODY io_thecodeforge.error_log_pkg AS
    PROCEDURE log_error(
        p_procedure_name VARCHAR2,
        p_sqlcode        NUMBER,
        p_sqlerrm        VARCHAR2,
        p_backtrace      VARCHAR2
    ) IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO error_log_tbl(
            procedure_name, error_code, error_message, backtrace, logged_at
        ) VALUES (
            p_procedure_name, p_sqlcode, p_sqlerrm, p_backtrace, SYSTIMESTAMP
        );
        COMMIT;
    END;
END;

The Execution Flow: What Happens When an Exception Is Raised?

You need to understand the sequence, not just the syntax. When an error occurs in the executable section of a PL/SQL block, control immediately jumps to the EXCEPTION section. The remaining code in the executable block is skipped entirely. No rollback happens automatically. Transaction control is your job. If the exception is handled, the block completes normally. If it's not handled, the exception propagates up to the calling block or client. This is why you must decide where to catch and where to let it bubble. A common production mistake: assuming the block resumes after handling. It does not. The executable block is aborted. Plan your logic accordingly.

// io.thecodeforge
DECLARE
   v_emp_id  employees.emp_id%TYPE := 9999;
   v_salary  employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_salary
   FROM employees
   WHERE emp_id = v_emp_id;

   UPDATE employees
   SET salary = v_salary * 1.1
   WHERE emp_id = v_emp_id;

   DBMS_OUTPUT.PUT_LINE('Salary updated successfully.');
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee ID not found. No rows updated.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
      ROLLBACK;
      RAISE;
END;
/

Logging Errors Without Losing the Stack: SQLCODE and SQLERRM

You cannot fix what you do not see. The implicit variables SQLCODE and SQLERRM give you the numeric error code and the full error message from the last raised exception. Use them. Always log them in your exception handler. Never rely on DBMS_OUTPUT.PUT_LINE alone; that goes to the client console and disappears. In production, write to an error log table. Wrap the logging in an autonomous transaction (PRAGMA AUTONOMOUS_TRANSACTION) so the log persists even if the parent transaction rolls back. Why? Because if you do not log, you are blind during postmortem. The junior who skips this will spend hours guessing what went wrong. You will spend minutes reading the log.

// io.thecodeforge
CREATE TABLE error_log (
   log_id       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   error_code   NUMBER,
   error_msg    VARCHAR2(4000),
   proc_name    VARCHAR2(100),
   log_time     TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE OR REPLACE PROCEDURE log_error(
   p_proc_name VARCHAR2
) AS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO error_log (error_code, error_msg, proc_name)
   VALUES (SQLCODE, SQLERRM, p_proc_name);
   COMMIT;
END;
/

-- Usage in a block:
DECLARE
   v_dividend NUMBER := 10;
   v_divisor  NUMBER := 0;
   v_result   NUMBER;
BEGIN
   v_result := v_dividend / v_divisor;
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      log_error('DIVISION_CALC');
      RAISE;
END;
/