This content originally appeared on DEV Community and was authored by Pranav Bakare
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.
This content originally appeared on DEV Community and was authored by Pranav Bakare
Pranav Bakare | Sciencx (2024-10-02T11:40:38+00:00) PRAGMA EXCEPTION_INIT in PLSQL. Retrieved from https://www.scien.cx/2024/10/02/pragma-exception_init-in-plsql/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.