Exception handling in PL/SQL

Exception handling in PL/SQL is a mechanism to manage runtime errors and handle exceptions gracefully in PL/SQL blocks, ensuring that the program can respond to errors without terminating unexpectedly.

What is Exception Handling in PL/SQL?

PL/SQL provides a way to define and manage exceptions using the EXCEPTION block. An exception is an error that occurs during program execution, which can disrupt the normal flow of execution. Exception handling allows developers to capture these errors, take corrective actions, or log them for further analysis.

How to Handle Exceptions?

There are several ways to handle exceptions in PL/SQL:

1. Predefined Exceptions: These are exceptions that are defined by PL/SQL, such as NO_DATA_FOUND, ZERO_DIVIDE, and TOO_MANY_ROWS. They can be handled directly by their names in the EXCEPTION section.

2. User-Defined Exceptions: Developers can define their exceptions using the RAISE statement. This allows custom error handling based on specific business logic.

3. Generic Exception Handling: This catches all exceptions not specifically handled by preceding exception handlers using the WHEN OTHERS clause.

Structure of Exception Handling

Here’s the general structure of a PL/SQL block with exception handling:

BEGIN
    -- Your executable code here
EXCEPTION
    WHEN predefined_exception_name THEN
        -- Handling code for predefined exception
    WHEN user_defined_exception_name THEN
        -- Handling code for user-defined exception
    WHEN OTHERS THEN
        -- Generic handling code for any other exceptions
END;

Examples of Exception Handling

Let's provide a demonstration of PL/SQL exception handling with examples that include both the code and the expected output.

Example 1: Handling Predefined Exceptions

Code:

DECLARE
    v_number NUMBER;
BEGIN
    -- Attempt to divide by zero
    v_number := 10 / 0;
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Error: Division by zero occurred.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Expected Output:

Error: Division by zero occurred

Example 2: Handling User-Defined Exceptions

Code:

DECLARE
    v_salary NUMBER := -1000;
    salary_error EXCEPTION;  -- User-defined exception
BEGIN
    IF v_salary < 0 THEN
        RAISE salary_error;  -- Raising the user-defined exception
    END IF;
    DBMS_OUTPUT.PUT_LINE('Salary is: ' || v_salary);
EXCEPTION
    WHEN salary_error THEN
        DBMS_OUTPUT.PUT_LINE('Error: Salary cannot be negative.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Expected Output:

Error: Salary cannot be negative.

Example 3: Using WHEN OTHERS for Generic Exception Handling

Code:

BEGIN
    -- Attempt to query a non-existent table
    EXECUTE IMMEDIATE 'SELECT * FROM non_existent_table';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Expected Output:

An unexpected error occurred: ORA-00942: table or view does not exist

Explanation of Output

1. Example 1: The division by zero triggers the ZERO_DIVIDE exception, and the program outputs an appropriate message.

2. Example 2: The user-defined exception for negative salary is raised and handled gracefully, resulting in a clear error message.

3. Example 3: Attempting to access a non-existent table raises an ORA-00942 error, demonstrating how to catch and log unexpected exceptions.

Summary

Exception handling is crucial for maintaining robust and error-tolerant PL/SQL programs.

PL/SQL allows both predefined and user-defined exceptions, enabling targeted error management.

The use of WHEN OTHERS provides a catch-all mechanism for unexpected errors, ensuring that the program can log or handle errors gracefully.

Enabling DBMS_OUTPUT in SQL*Plus or SQL Developer

To see the output from the DBMS_OUTPUT.PUT_LINE statements, ensure that DBMS_OUTPUT is enabled in your SQL environment:

In SQL*Plus, run the command SET SERVEROUTPUT ON;
In SQL Developer, click on the DBMS Output tab and enable it.

By following these steps, you can execute the provided code snippets and observe the exception handling in action.