Exceptional Handling in PLSQL

In PL/SQL (Procedural Language/Structured Query Language), Oracle provides a robust exception handling mechanism to deal with errors and unexpected conditions during the execution of programs. Exception handling allows developers to handle runtime errors, ensuring that the application does not crash abruptly and allowing developers to take corrective actions or display user-friendly error messages.

What is an Exception?

An exception is an error condition or unexpected event that occurs during the execution of a PL/SQL block. There are two types of exceptions:

1. Predefined Exceptions: These are exceptions automatically raised by Oracle when standard errors occur (like NO_DATA_FOUND, ZERO_DIVIDE, TOO_MANY_ROWS, etc.).

2. User-defined Exceptions: These are exceptions that the developer can define and raise explicitly using the RAISE keyword.

Exception Handling Structure in PL/SQL

The EXCEPTION keyword is used to define an exception-handling section in a PL/SQL block. It is part of a PL/SQL block's structure, which typically follows this order:

1. Declaration Section: Variables, cursors, and user-defined exceptions are declared here.

2. Execution Section: Contains the executable code where actual SQL statements or PL/SQL logic are implemented.

3. Exception Section: This is where errors are caught and handled.

Syntax

DECLARE
   -- Declaration section
   -- Variables, cursors, user-defined exceptions are declared here.

BEGIN
   -- Execution section
   -- Contains the code which might raise an exception.

EXCEPTION
   -- Exception section
   WHEN exception_name1 THEN
      -- Code to handle the exception
   WHEN exception_name2 THEN
      -- Code to handle another exception
   WHEN OTHERS THEN
      -- Handles all other exceptions not explicitly named
END;

Types of Exceptions

1. Predefined Exceptions: These are exceptions that Oracle has already defined, and they are automatically raised when certain standard errors occur.

Some common predefined exceptions include:

NO_DATA_FOUND: Raised when a SELECT INTO statement does not return any 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 a cursor operation is attempted on a closed or invalid cursor.

Example of handling a predefined exception:

BEGIN
   -- Attempt to divide by zero
   DECLARE
      v_result NUMBER;
   BEGIN
      v_result := 100 / 0;
   EXCEPTION
      WHEN ZERO_DIVIDE THEN
         DBMS_OUTPUT.PUT_LINE('Division by zero is not allowed.');
   END;
END;

2. User-defined Exceptions:

These are exceptions that are explicitly defined by the programmer using the EXCEPTION keyword and raised using the RAISE keyword.

Example:

DECLARE
   v_salary NUMBER := 5000;
   salary_too_low EXCEPTION;  -- User-defined exception
BEGIN
   -- Check for a condition and raise a user-defined exception
   IF v_salary < 1000 THEN
      RAISE salary_too_low;  -- Raise the user-defined exception
   END IF;
EXCEPTION
   WHEN salary_too_low THEN
      DBMS_OUTPUT.PUT_LINE('Error: Salary is too low.');
END;

3. OTHERS Exception:

The OTHERS exception is a catch-all exception handler that captures any exceptions not explicitly named in the WHEN clauses. It should be placed at the end of the EXCEPTION block.

Example:

BEGIN
   -- Code that might raise various exceptions
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;

Here, SQLERRM is a built-in function that returns the error message for the last raised exception.

PL/SQL Exception Propagation

Exceptions can be propagated from a sub-block to an enclosing block. If an exception occurs in a sub-block and is not handled there, it will be propagated to the enclosing block. If the exception remains unhandled at all levels, the execution terminates, and an error is returned to the calling environment.

Example of exception propagation:

DECLARE
   v_salary NUMBER := 5000;
BEGIN
   DECLARE
      salary_too_low EXCEPTION;  -- User-defined exception
   BEGIN
      -- Raising a user-defined exception
      IF v_salary < 10000 THEN
         RAISE salary_too_low;
      END IF;
   EXCEPTION
      WHEN salary_too_low THEN
         DBMS_OUTPUT.PUT_LINE('Sub-block handling: Salary is too low.');
         RAISE;  -- Re-raising the exception
   END;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Enclosing block handling: ' || SQLERRM);
END;

Exception Information

When an exception occurs, Oracle provides error information through two built-in functions:

1. SQLCODE: Returns the numeric error code of the most recent exception.

2. SQLERRM: Returns the error message for the most recent exception.

Example:

BEGIN
   DECLARE
      v_dividend NUMBER := 10;
      v_divisor NUMBER := 0;
      v_result NUMBER;
   BEGIN
      v_result := v_dividend / v_divisor;
   EXCEPTION
      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
         DBMS_OUTPUT.PUT_LINE('Error message: ' || SQLERRM);
   END;
END;

In this example, when the division by zero occurs, the exception is caught, and both the error code and message are displayed.

Important Predefined Exceptions in Oracle

Here are some important predefined exceptions and their error codes:

Key Exception Handling Concepts

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

2. Raising Predefined Exceptions: Predefined exceptions are automatically raised by Oracle, but they can also be explicitly raised using the RAISE statement.

3. RAISE_APPLICATION_ERROR: This is used to create a user-defined error message and raise it to the calling environment. It is commonly used in procedures and functions.

Syntax:

RAISE_APPLICATION_ERROR(error_number, error_message);

Example:

BEGIN
   IF some_condition THEN
      RAISE_APPLICATION_ERROR(-20001, 'A custom error message.');
   END IF;
END;

4. PRAGMA EXCEPTION_INIT: This allows you to associate an Oracle error number with a user-defined exception. This is useful for handling specific Oracle errors using custom names.

Example:

DECLARE
   invalid_number EXCEPTION;
   PRAGMA EXCEPTION_INIT(invalid_number, -01722);  
-- Associating Oracle error 1722 (Invalid Number) with a 
-- user-defined exception
BEGIN
   -- Code that might raise the invalid number error
EXCEPTION
   WHEN invalid_number THEN
      DBMS_OUTPUT.PUT_LINE('Invalid number error occurred.');
END;

Conclusion

The PL/SQL exception handling mechanism is a powerful tool that allows developers to handle both predefined and user-defined exceptions effectively. By using the EXCEPTION block along with mechanisms like RAISE, RAISE_APPLICATION_ERROR, and PRAGMA EXCEPTION_INIT, developers can catch and handle errors, ensuring that programs run smoothly and handle errors gracefully. This improves the robustness of applications and ensures that even in the event of errors, the application can provide meaningful feedback to users or take corrective actions.