PL/SQL Exception Types

PL/SQL (Procedural Language/Structured Query Language) is Oracle’s extension to SQL, allowing for the integration of procedural programming constructs within the database envir

onment. One crucial aspect of building reliable and maintainable PL/SQL applications is exception handling. Errors are inevitable, but how they are handled within your code determines the resilience and robustness of the application. PL/SQL Exception Types allow developers to manage these situations gracefully by categorising exceptions into predefined and user-defined exceptions.

In this article, we will explore the various PL/SQL Exception Types, how they work, how to handle exceptions in PL/SQL, and illustrate these concepts with PL/SQL Exception Handling Examples. By the end, you’ll have a comprehensive understanding of exception types in PL/SQL and how to implement them effectively.

What is an Exception in PL/SQL?

An exception is a runtime error, an unexpected situation that arises during the execution of a PL/SQL program. Normal execution is terminated, and control passes to the corresponding exception-handling section of the code when an exception arises. If not handled properly, errors cause abrupt termination of programs, leading to a poor user experience or even data corruption.

There are two main types of exceptions in PL/SQL:

• Predefined Exceptions:These are inherent exceptions that Oracle raises automatically in case of any predefined standard error conditions.
• User-Defined Exceptions: The exceptions, on the other hand, are explicitly defined by the developer to handle specific business or application logic.

Why Handle Exceptions?

Avoid program crashes: Exception handling will ensure that your application keeps running despite the unexpected issues.
Give meaningful error messages: Instead of very cryptic or technical error messages, you can let the users know what went wrong, in a user-friendly way.
Ensure Data Consistency: Proper exception handling prevents data corruption or even complete transactions.

PL/SQL Exception Types

PL/SQL defines a wide range of exception types to manage errors that occur during the execution of PL/SQL blocks. These types are categorized into two groups:

1. Predefined Exceptions: These exceptions are automatically raised by the PL/SQL engine for common error conditions.
2. User-Defined Exceptions: Developers can define custom exceptions to handle specific error scenarios.

Let’s explore these in more detail.

Predefined Exceptions in PL/SQL

Predefined exceptions are automatically raised by Oracle when an error occurs. These exceptions are built into the language and can be handled using the WHEN clause in the EXCEPTION block.

Common Predefined Exceptions in PL/SQL

Exception Name	Error Code	Description
NO_DATA_FOUND	ORA-01403	Raised when a SELECT INTO statement returns no rows.
TOO_MANY_ROWS	ORA-01422	Raised when a SELECT INTO statement returns more than one row.
ZERO_DIVIDE	ORA-01476	Raised when dividing a number by zero.
INVALID_CURSOR	ORA-01001	Raised when an invalid cursor operation is attempted.
DUP_VAL_ON_INDEX	ORA-00001	Raised when a duplicate value is attempted to be inserted into a unique index.
CURSOR_ALREADY_OPEN	ORA-06511	Raised when an attempt is made to open an already open cursor.

Example of Handling Predefined Exceptions

DECLARE
  v_emp_id employees.employee_id%TYPE := 100;
  v_emp_name employees.employee_name%TYPE;
BEGIN
  -- Try to fetch employee name based on employee ID
  SELECT employee_name INTO v_emp_name
  FROM employees
  WHERE employee_id = v_emp_id;

  -- Output the employee name
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Error: No employee found with ID ' || v_emp_id);
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Error: More than one employee found with ID ' || v_emp_id);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;

In this example, predefined exceptions like NO_DATA_FOUND and TOO_MANY_ROWS are used to handle specific conditions in the SELECT INTO statement.

User-Defined Exceptions in PL/SQL

While predefined exceptions handle common error scenarios, user-defined exceptions allow developers to define custom error conditions that are specific to their applications.

Syntax for Declaring User-Defined Exceptions

1. Declare the Exception: A user-defined exception is declared in the DECLARE section of a PL/SQL block.
2. Raise the Exception: Use the RAISE statement to explicitly trigger the exception.
3. Handle the Exception: Implement exception handling logic in the EXCEPTION section.

Example of User-Defined Exceptions

DECLARE
  v_salary employees.salary%TYPE;
  salary_too_low EXCEPTION;  -- Declare user-defined exception

BEGIN
  -- Fetch salary of an employee
  SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;

  -- Raise the exception if salary is below the threshold
  IF v_salary < 3000 THEN
    RAISE salary_too_low;
  END IF;

  DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || v_salary);

EXCEPTION
  WHEN salary_too_low THEN
    DBMS_OUTPUT.PUT_LINE('Error: Salary is too low!');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;

In this example, the salary_too_low exception is user-defined to manage scenarios where an employee’s salary is below a certain threshold.

Handling Exceptions in PL/SQL

PL/SQL provides a structured way to handle exceptions using the BEGIN...EXCEPTION...END block. Exceptions can be handled using predefined or user-defined error handlers. The general structure is:

DECLARE
  -- Variable and exception declarations
BEGIN
  -- Main program logic
EXCEPTION
  WHEN predefined_exception THEN
    -- Code to handle predefined exception
  WHEN user_defined_exception THEN
    -- Code to handle user-defined exception
  WHEN OTHERS THEN
    -- Code to handle all other exceptions
END;

The WHEN OTHERS clause is a catch-all that captures any exceptions not explicitly listed. It’s considered good practice to always include a WHEN OTHERS clause to handle unexpected exceptions.

Example of Exception Handling

DECLARE
  v_dept_id departments.department_id%TYPE := 1000;
  dept_not_found EXCEPTION;  -- Declare user-defined exception

BEGIN
  -- Fetch department name based on department ID
  SELECT department_name INTO v_dept_name
  FROM departments
  WHERE department_id = v_dept_id;

  -- If no data is found, raise the user-defined exception
  IF SQL%NOTFOUND THEN
    RAISE dept_not_found;
  END IF;

  DBMS_OUTPUT.PUT_LINE('Department Name: ' || v_dept_name);

EXCEPTION
  WHEN dept_not_found THEN
    DBMS_OUTPUT.PUT_LINE('Error: No department found with ID ' || v_dept_id);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;

PL/SQL Exception Handling Examples

Now that we have a foundation of predefined and user-defined exceptions, let’s look at a more comprehensive example.

Example: Handling Multiple Predefined and User-Defined Exceptions

DECLARE
  v_emp_id employees.employee_id%TYPE := 2001;
  v_salary employees.salary%TYPE;
  low_salary EXCEPTION;  -- Declare a user-defined exception

BEGIN
  -- Fetch employee salary
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_emp_id;

  -- Check if the salary is too low and raise a user-defined exception
  IF v_salary < 2000 THEN
    RAISE low_salary;
  END IF;

  DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || v_salary);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Error: No employee found with ID ' || v_emp_id);
  WHEN low_salary THEN
    DBMS_OUTPUT.PUT_LINE('Error: Employee salary is too low.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;

In this example, multiple predefined and user-defined exceptions are used. The program tries to fetch an employee’s salary and handles cases where no employee is found, the salary is too low, or an unexpected error occurs.

Table of PL/SQL Exception Types

Exception Type	Description
Predefined Exceptions	Built-in exceptions raised automatically by Oracle when certain conditions are met (e.g., NO_DATA_FOUND, ZERO_DIVIDE).
User-Defined Exceptions	Custom exceptions defined by developers to handle specific business logic.
Named Exceptions	Predefined or user-defined exceptions that have been given a specific name for easier reference.

Best Practices for Exception Handling

1. Use Specific Exceptions: Handle specific exceptions such as NO_DATA_FOUND rather than relying on the catch-all WHEN OTHERS clause.
2. Avoid Silent Failures: Ensure exceptions are logged or communicated to users appropriately instead of being ignored.
3. Use Custom Messages: Provide detailed error messages to users or system logs for easier debugging.
4. Close Resources: Always ensure that cursors and other resources are closed in the event of an exception.
5. Use RAISE_APPLICATION_ERROR: For user-defined exceptions, use the RAISE_APPLICATION_ERROR procedure to generate meaningful custom error messages.