DEV Community

Pranav Bakare
Pranav Bakare

Posted on

Exception handling in PLSQL | EXCEPTION keyword | PART 1

#beginners #programming #tutorial #plsql

Exception handling in PLSQL with EXCEPTION keyword

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.

A PL/SQL block typically consists of three sections:

  1. Declaration Section: Here, you define variables, cursors, and exceptions.

  2. Execution Section: This is where you place the executable statements.

  3. 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;
Enter fullscreen mode Exit fullscreen mode

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.

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;
Enter fullscreen mode Exit fullscreen mode

Explanation

1. Declaration Section:

  • Declares variables for salary, bonus, and total salary.
  • Defines a user-defined exception v_salary_exception.

2. 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.

3. 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.

Top comments (0)