The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Exception Handling

Next ►
►

Exception handling in PL/SQL is a crucial concept that allows developers to manage and respond to runtime errors effectively. This section will cover the basics of exception handling, including predefined exceptions, user-defined exceptions, and how to use the EXCEPTION block to handle errors gracefully.

Key Concepts

  1. Exceptions: An exception is an error condition during a program's execution.
  2. Predefined Exceptions: These are standard exceptions provided by PL/SQL.
  3. User-Defined Exceptions: These are custom exceptions defined by the programmer.
  4. Exception Handling Block: The part of the PL/SQL block where exceptions are caught and handled.

Exception Handling Block Structure

A PL/SQL block with exception handling typically follows this structure:

DECLARE
   -- Declarations
BEGIN
   -- Executable statements
EXCEPTION
   -- Exception handling statements
END;

Predefined Exceptions

PL/SQL provides several predefined exceptions that you can use to handle common errors. Here are some of the most commonly used predefined exceptions:

Exception Name Description
NO_DATA_FOUND Raised when a SELECT INTO statement returns no rows.
TOO_MANY_ROWS Raised when a SELECT INTO statement returns more than one row.
ZERO_DIVIDE Raised when an attempt is made to divide a number by zero.
INVALID_CURSOR Raised when an illegal cursor operation is attempted.
VALUE_ERROR Raised when an arithmetic, conversion, truncation, or size constraint error occurs.

Example: Handling Predefined Exceptions

DECLARE
   v_number NUMBER;
BEGIN
   v_number := 10 / 0; -- This will raise a ZERO_DIVIDE exception
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
END;

Explanation

  • DECLARE: Declares a variable v_number.
  • BEGIN: Starts the executable part of the block.
  • v_number := 10 / 0;: This line will raise a ZERO_DIVIDE exception.
  • EXCEPTION: Starts the exception handling part of the block.
  • WHEN ZERO_DIVIDE THEN: Catches the ZERO_DIVIDE exception and executes the associated code.
  • DBMS_OUTPUT.PUT_LINE: Outputs an error message.

User-Defined Exceptions

You can define your own exceptions to handle specific error conditions in your application.

Steps to Define and Handle User-Defined Exceptions

  1. Declare the exception in the DECLARE section.
  2. Raise the exception using the RAISE statement.
  3. Handle the exception in the EXCEPTION block.

Example: User-Defined Exception

DECLARE
   e_custom_exception EXCEPTION;
   v_number NUMBER := 10;
BEGIN
   IF v_number > 5 THEN
      RAISE e_custom_exception;
   END IF;
EXCEPTION
   WHEN e_custom_exception THEN
      DBMS_OUTPUT.PUT_LINE('Error: Custom exception raised because v_number is greater than 5.');
END;

Explanation

  • DECLARE: Declares a user-defined exception e_custom_exception and a variable v_number.
  • BEGIN: Starts the executable part of the block.
  • IF v_number > 5 THEN RAISE e_custom_exception; END IF;: Raises the e_custom_exception if v_number is greater than 5.
  • EXCEPTION: Starts the exception handling part of the block.
  • WHEN e_custom_exception THEN: Catches the e_custom_exception and executes the associated code.
  • DBMS_OUTPUT.PUT_LINE: Outputs an error message.

Practical Exercises

Exercise 1: Handling Predefined Exceptions

Task: Write a PL/SQL block that attempts to fetch a row from a table that does not exist and handles the NO_DATA_FOUND exception.

DECLARE
   v_name VARCHAR2(50);
BEGIN
   SELECT name INTO v_name FROM employees WHERE employee_id = 9999;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Error: No data found for the given employee ID.');
END;

Solution

  • DECLARE: Declares a variable v_name.
  • BEGIN: Starts the executable part of the block.
  • SELECT name INTO v_name FROM employees WHERE employee_id = 9999;: Attempts to fetch a row that does not exist, raising a NO_DATA_FOUND exception.
  • EXCEPTION: Starts the exception handling part of the block.
  • WHEN NO_DATA_FOUND THEN: Catches the NO_DATA_FOUND exception and executes the associated code.
  • DBMS_OUTPUT.PUT_LINE: Outputs an error message.

Exercise 2: User-Defined Exception

Task: Write a PL/SQL block that raises a user-defined exception if a variable v_age is less than 18.

DECLARE
   e_underage EXCEPTION;
   v_age NUMBER := 16;
BEGIN
   IF v_age < 18 THEN
      RAISE e_underage;
   END IF;
EXCEPTION
   WHEN e_underage THEN
      DBMS_OUTPUT.PUT_LINE('Error: Age is less than 18.');
END;

Solution

  • DECLARE: Declares a user-defined exception e_underage and a variable v_age.
  • BEGIN: Starts the executable part of the block.
  • IF v_age < 18 THEN RAISE e_underage; END IF;: Raises the e_underage exception if v_age is less than 18.
  • EXCEPTION: Starts the exception handling part of the block.
  • WHEN e_underage THEN: Catches the e_underage exception and executes the associated code.
  • DBMS_OUTPUT.PUT_LINE: Outputs an error message.

Common Mistakes and Tips

  • Not Handling Exceptions: Always handle exceptions to avoid unexpected program termination.
  • Overusing Exceptions: Use exceptions for error conditions, not for regular control flow.
  • Not Logging Errors: Log errors for debugging and auditing purposes.

Conclusion

In this section, you learned about exception handling in PL/SQL, including predefined and user-defined exceptions. You also practiced writing PL/SQL blocks that handle exceptions gracefully. Understanding and implementing proper exception handling is essential for building robust and reliable PL/SQL applications. In the next module, we will explore how to work with SQL statements within PL/SQL.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved