In PL/SQL, which is Oracle's procedural extension to SQL, exception handling is an essential feature that allows you to manage errors and exceptions that occur during the execution of a program. The EXCEPTION keyword is used in PL/SQL to define the exception handling section of a block of code. Below is a detailed explanation of how to implement exception handling using the EXCEPTION keyword in PL/SQL, along with an example.

Exception Handling Structure in PL/SQL

A PL/SQL block typically consists of three sections:

Declaration Section: Here, you define variables, cursors, and exceptions.
Execution Section: This is where you place the executable statements.
Exception Section: This is where you handle exceptions using the EXCEPTION keyword.

Syntax

DECLARE
-- Declaration section
v_variable datatype; -- Variable declarations
v_exception EXCEPTION; -- User-defined exception
BEGIN
-- Execution section
-- Your executable statements go here
EXCEPTION
WHEN predefined_exception THEN
-- Actions to take when a predefined exception occurs
WHEN v_exception THEN
-- Actions to take when a user-defined exception occurs
WHEN OTHERS THEN
-- Actions to take for any other exceptions
END;

Key Points

Predefined Exceptions: PL/SQL provides several predefined exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, etc. You can handle these exceptions directly in the exception section.

User-defined Exceptions: You can also define your own exceptions using the EXCEPTION keyword, as shown above.

OTHERS: The WHEN OTHERS clause is a catch-all for any exceptions not explicitly handled.

Example

Here’s a simple example demonstrating exception handling in PL/SQL:

DECLARE
v_salary NUMBER := 5000;
v_bonus NUMBER;
v_total_salary NUMBER;

-- User-defined exception
v_salary_exception EXCEPTION;

BEGIN
-- Calculation that might raise an exception
IF v_salary < 0 THEN
RAISE v_salary_exception; -- Raise user-defined exception
END IF;

-- Some calculations
v_bonus := v_salary * 0.1;  -- 10% bonus
v_total_salary := v_salary + v_bonus;

DBMS_OUTPUT.PUT_LINE('Total Salary: ' || v_total_salary);

EXCEPTION
WHEN v_salary_exception THEN
DBMS_OUTPUT.PUT_LINE('Error: Salary cannot be negative.');
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero encountered.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;

Explanation

Declaration Section:

Declares variables for salary, bonus, and total salary.

Defines a user-defined exception v_salary_exception.

Execution Section:

Checks if v_salary is negative; if so, it raises the v_salary_exception.

Calculates the bonus and total salary if no exception occurs.

Exception Section:

Handles the v_salary_exception by printing a custom error message.

Catches the ZERO_DIVIDE predefined exception.

Catches any other unexpected exceptions with WHEN OTHERS and uses SQLERRM to retrieve the error message.

Conclusion

Using the EXCEPTION keyword in PL/SQL allows for robust error handling, making your code more resilient to runtime errors. You can manage both predefined and user-defined exceptions effectively, ensuring that your applications handle errors gracefully.

Exception handling in PLSQL with EXCEPTION keyword