Exception Handling in PL/SQL| PRAGMA EXCEPTION_INIT | Part 3

#plsql #sql #data #oracle

PRAGMA EXCEPTION_INIT

In PL/SQL, the PRAGMA EXCEPTION_INIT compiler directive allows you to associate a user-defined exception with a specific Oracle error number. This helps you to handle Oracle errors more gracefully and map them to meaningful exception names in your PL/SQL programs.

1) What is PRAGMA EXCEPTION_INIT?

In the context of PL/SQL, PRAGMA EXCEPTION_INIT is used to map a predefined Oracle error number to a user-defined exception. This allows for easier and more readable error handling when a specific Oracle error occurs. Let's walk through this concept using a simple example in detail.

2)Syntax of PRAGMA EXCEPTION_INIT:

PRAGMA EXCEPTION_INIT(exception_name, -error_number);

exception_name: A user-defined exception that you declare in the PL/SQL block.

error_number: The Oracle error number (a negative number, such as -1403 for NO_DATA_FOUND) you want to associate with the exception.

3)Step-by-step guide on how to use PRAGMA EXCEPTION_INIT for exception handling:

1.Declare a User-Defined Exception: Define an exception that will correspond to a specific Oracle error.

2. Associate the Exception with an Oracle Error Number: Use PRAGMA EXCEPTION_INIT to associate the exception with an Oracle error number.

3. Handle the Exception: Use the WHEN clause in an EXCEPTION block to handle the exception.

4)Example

Suppose you want to handle the NO_DATA_FOUND exception (which is typically Oracle error number ORA-01403), but you want to map it to a user-defined exception for more readable code.

Let's break this down step by step:

1. Create a table: We'll create a simple employees table with employee_id and employee_name.

2. Insert data: We'll insert a few records into the employees table.

3. Perform the exceptional mechanism: We'll then try to select a non-existing record and handle the NO_DATA_FOUND exception using PRAGMA EXCEPTION_INIT.

Step 1: Create the employees table

CREATE TABLE employees (
    employee_id NUMBER(5) PRIMARY KEY,
    employee_name VARCHAR2(100)
);

Step 2: Insert data into the table

INSERT INTO employees (employee_id, employee_name) 
VALUES (1001, 'John Doe');
INSERT INTO employees (employee_id, employee_name) 
VALUES (1002, 'Jane Smith');
INSERT INTO employees (employee_id, employee_name) 
VALUES (1003, 'Mark Johnson');

Step 3: Perform the exceptional mechanism

Here is the PL/SQL block where we try to select a non-existing employee_id and handle the NO_DATA_FOUND exception using PRAGMA EXCEPTION_INIT.


DECLARE
-- Step 1: Declare a user-defined exception
    no_data_error EXCEPTION;

-- Step 2: Associate the user-defined exception with an Oracle error number
    PRAGMA EXCEPTION_INIT(no_data_error, -1403);

    v_employee_name VARCHAR2(100);
BEGIN
-- Attempt to select an employee with a non-existing 
-- employee_id (e.g., 9999)
    SELECT employee_name INTO v_employee_name
    FROM employees
    WHERE employee_id = 9999;  -- This employee ID doesn't exist

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);

EXCEPTION
 -- Step 3: Handle the user-defined exception
    WHEN no_data_error THEN
        DBMS_OUTPUT.PUT_LINE('No data found for the given employee ID.');
END;
/

Expected Output:

No data found for the given employee ID.

5)Explanation:

Exception handling: The PL/SQL block attempts to select an employee with employee_id = 9999, which does not exist. Since no data is found, the Oracle error ORA-01403 is raised.

We mapped this error to our user-defined exception no_data_error using PRAGMA EXCEPTION_INIT, and the exception block catches it, printing the message: "No data found for the given employee ID.