Error Handling

Error handling in PL/SQL allows you to detect, manage, and respond to runtime errors gracefully, ensuring that your application remains robust and user-friendly.

1. Exception Types

In PL/SQL, exceptions represent error conditions. They can be predefined exceptions (built-in) or user-defined exceptions (custom).

1.1 Predefined Exceptions

Predefined exceptions are built into PL/SQL and are automatically raised when specific Oracle errors occur.

Example: Handling Predefined Exceptions

plsql
Copy code
BEGIN
   SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999; -- No such employee
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Error: No data found for the given employee ID.');
END;
/

1.2 User-Defined Exceptions

User-defined exceptions are explicitly declared and raised by the programmer for specific application logic.

Syntax:

plsql
Copy code
DECLARE
   my_exception EXCEPTION; -- Declare a user-defined exception
BEGIN
   IF some_condition THEN
      RAISE my_exception; -- Raise the exception
   END IF;
EXCEPTION
   WHEN my_exception THEN
      -- Handle the exception
END;
/

Example:

plsql
Copy code
DECLARE
   insufficient_funds EXCEPTION;
   v_balance NUMBER := 500;
   v_withdrawal NUMBER := 1000;
BEGIN
   IF v_withdrawal > v_balance THEN
      RAISE insufficient_funds;
   END IF;
EXCEPTION
   WHEN insufficient_funds THEN
      DBMS_OUTPUT.PUT_LINE('Error: Insufficient funds.');
END;
/

2. Raising Exceptions (RAISE)

The RAISE statement is used to explicitly raise an exception, either predefined or user-defined.

Syntax:

plsql
Copy code
RAISE exception_name;

Example: Raising Predefined Exceptions

plsql
Copy code
DECLARE
   v_divisor NUMBER := 0;
BEGIN
   IF v_divisor = 0 THEN
      RAISE ZERO_DIVIDE;
   END IF;
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Error: Division by zero.');
END;
/

3. Exception Handling with WHEN OTHERS

The WHEN OTHERS exception handler catches any exceptions that are not explicitly handled. It should typically be used as the last handler in an exception block.

Syntax:

plsql
Copy code
EXCEPTION
   WHEN OTHERS THEN
      -- Handle any other exceptions
END;

Example:

plsql
Copy code
DECLARE
   v_result NUMBER;
BEGIN
   v_result := 10 / 0; -- This will raise ZERO_DIVIDE
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation:

• SQLERRM returns the error message for the last exception.
• Use WHEN OTHERS cautiously to avoid hiding specific errors.

4. Example: Error Handling in a Banking Application

Scenario:

A banking application processes account withdrawals. It should handle:

1. Withdrawal amount exceeding balance (user-defined exception).
2. Division by zero when calculating charges (predefined exception).
3. Any unexpected errors.

Code Example:

plsql
Copy code
DECLARE
   insufficient_funds EXCEPTION;
   v_balance NUMBER := 1000;
   v_withdrawal NUMBER := 1200;
   v_charge NUMBER := 0;
   v_fee NUMBER;

BEGIN
   -- Check for sufficient funds
   IF v_withdrawal > v_balance THEN
      RAISE insufficient_funds;
   END IF;

   -- Calculate transaction fee
   v_fee := v_withdrawal / v_charge; -- This will raise ZERO_DIVIDE

   -- Deduct balance
   v_balance := v_balance - v_withdrawal - v_fee;
   DBMS_OUTPUT.PUT_LINE('Transaction successful. Remaining balance: ' || v_balance);

EXCEPTION
   WHEN insufficient_funds THEN
      DBMS_OUTPUT.PUT_LINE('Error: Insufficient funds for the withdrawal.');
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Error: Transaction fee calculation failed due to division by zero.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

5. Best Practices for Exception Handling

1. Use Specific Exceptions First:
   • Handle specific exceptions before using WHEN OTHERS.
2. Log Errors:
   • Log errors using DBMS_OUTPUT or a logging mechanism for debugging.
3. Avoid Overusing WHEN OTHERS:
   • It should not suppress all errors; use it for truly unexpected situations.
4. Keep Exception Handlers Simple:
   • Focus on error resolution or logging instead of complex operations.
5. Rethrow Exceptions if Necessary:
   • Use RAISE in the exception block to propagate the error upward.

Summary

Error handling in PL/SQL allows you to write robust and maintainable code by managing runtime errors effectively.

By mastering these concepts, you can build applications that respond gracefully to errors and provide meaningful feedback to users.