Question

Explain exception handling in PL/SQL with example.

Answer 1

Of course. Here is a comprehensive explanation of exception handling in PL/SQL, complete with its purpose, structure, types of exceptions, and a detailed, practical example.

---

What is Exception Handling?

Exception Handling is a powerful mechanism in PL/SQL that allows you to gracefully manage runtime errors, called exceptions. An exception is an event that occurs during program execution that disrupts the normal flow of instructions. Examples include trying to divide by zero, fetching data into a variable that doesn't exist, or violating a database rule.

Instead of letting these errors crash your program, exception handling provides a "safety net" to catch the error and execute a specific block of code to handle it, such as logging the error, displaying a user-friendly message, or attempting to recover.

The PL/SQL Block and the EXCEPTION Section

The structure for handling exceptions is built directly into the standard PL/SQL block:

`sql
DECLARE

-- (Optional) Declarations of variables, cursors, etc.

BEGIN

-- (Mandatory) The main executable logic.
-- An exception might be raised here.

EXCEPTION

-- (Optional) The error-handling section.
-- If an error occurs in the BEGIN block, control jumps here.
WHEN exception_name1 THEN
    -- Code to handle the first specific error
WHEN exception_name2 THEN
    -- Code to handle a second specific error
WHEN OTHERS THEN
    -- Code to handle all other unexpected errors

END;
/
`

If an error occurs in the BEGIN section, the normal execution stops immediately, and the program jumps to the EXCEPTION section to find a matching handler.

Types of Exceptions in PL/SQL

There are three main categories of exceptions:

1. Predefined Exceptions: These are common, system-defined errors that have been given names by Oracle. There are about 20 of them. Some of the most frequent are:
   NO_DATA_FOUND: A SELECT ... INTO statement returns no rows.
   TOO_MANY_ROWS: A SELECT ... INTO statement returns more than one row.
   ZERO_DIVIDE: An attempt to divide a number by zero.
   INVALID_CURSOR: An illegal cursor operation was attempted (e.g., fetching from a closed cursor).

2. User-Defined Exceptions: You can declare your own exceptions in the DECLARE section to handle specific business rule violations. You must explicitly RAISE these exceptions in your code.

3. Non-Predefined Exceptions: These are other standard Oracle errors that do not have a predefined name. You can still handle them by using the WHEN OTHERS clause or by assigning a name to the specific Oracle error code using PRAGMA EXCEPTION_INIT.

---

Complete Example

Let's write a PL/SQL block that retrieves an employee's name based on their ID. We will handle the cases where the ID is invalid (NO_DATA_FOUND) and where the query might return multiple people (e.g., if we queried by a non-unique column).

Step 1: Sample Data (if needed)

Assume we have a standard employees table like this:
| employee_id | first_name | last_name | department_id |
| :--- | :--- | :--- | :--- |
| 100 | Steven | King | 90 |
| 101 | Neena | Kochhar | 90 |
| 205 | Shelley | Higgins | 110 |

Step 2: The PL/SQL Program

This block demonstrates how to handle NO_DATA_FOUND, TOO_MANY_ROWS, and any other unexpected errors.

`sql
-- Enable output to see the results
SET SERVEROUTPUT ON;

DECLARE

-- Variable to hold the employee ID we are searching for
v_search_id      employees.employee_id%TYPE := 101; 

-- Variable to hold the name we retrieve
v_employee_name  VARCHAR2(100);

BEGIN

-- This is the "risky" section where errors can occur.
DBMS_OUTPUT.PUT_LINE('Attempting to find employee with ID: ' || v_search_id);

-- This query will raise an exception if v_search_id is invalid or not unique.
SELECT first_name || ' ' || last_name
INTO v_employee_name
FROM employees
WHERE employee_id = v_search_id;

-- This line will only run if the SELECT statement is successful.
DBMS_OUTPUT.PUT_LINE('Success! Employee found: ' || v_employee_name);

-- This is the exception handling block.
EXCEPTION

-- Handler for the "no data found" error.
WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Error: No employee exists with ID ' || v_search_id);
    DBMS_OUTPUT.PUT_LINE('------------------------------------');

-- Handler for the "too many rows" error.
-- (This wouldn't happen when searching by a primary key, but is shown for example).
WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Error: The query returned multiple employees. This is a system error.');
    DBMS_OUTPUT.PUT_LINE('------------------------------------');

-- A catch-all handler for any other unexpected errors.
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('------------------------------------');
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
    -- SQLCODE and SQLERRM are special functions that give the error code and message.
    DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('------------------------------------');

END;
/
`

How to Test the Example

You can test the different error scenarios by changing the value of v_search_id in the DECLARE section.

Test Case 1: A Valid ID (Success Path)
Set v_search_id := 101;
Output:

```
Attempting to find employee with ID: 101
Success! Employee found: Neena Kochhar
```

Test Case 2: An Invalid ID (NO_DATA_FOUND)
Set v_search_id := 999;
Output: The SELECT statement fails, and control jumps to the EXCEPTION block.

```
Attempting to find employee with ID: 999
------------------------------------
Error: No employee exists with ID 999
------------------------------------
```

Test Case 3: A Non-Unique Query (TOO_MANY_ROWS)
To test this, we would need to change the query to search on a non-unique column, like department_id.
Change the DECLARE and BEGIN sections to:

```sql
DECLARE
    v_search_dept_id employees.department_id%TYPE := 90;
    v_employee_name  VARCHAR2(100);
BEGIN
    SELECT first_name INTO v_employee_name FROM employees WHERE department_id = v_search_dept_id;
...
```

• Output: The SELECT statement finds multiple employees in department 90 and tries to fit them into a single variable, which raises the TOO_MANY_ROWS exception.

  `

  Error: The query returned multiple employees. This is a system error.

  `