RAISE_APPLICATION_ERROR and PRAGMA EXCEPTION_INIT in PLSQL EXCEPT HANDLING

In Oracle PL/SQL, RAISE_APPLICATION_ERROR and PRAGMA EXCEPTION_INIT are used for handling exceptions, but they serve different purposes:

1. RAISE_APPLICATION_ERROR:

Purpose: This is used to explicitly raise a custom error from your PL/SQL code. You can define your own error message and error number (in the range of -20000 to -20999).

Use Case: When you want to handle specific conditions in your code and provide a user-friendly error message, you can use RAISE_APPLICATION_ERROR.

Example:

BEGIN
IF salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative.');
END IF;
END;

In this example, if the salary is less than 0, it raises an error with the custom message "Salary cannot be negative."

When using RAISE_APPLICATION_ERROR, you must catch it in a general EXCEPTION block (WHEN OTHERS) and use the SQLCODE to identify the specific error.

When you use RAISE_APPLICATION_ERROR, it generates a custom error that can be caught in an EXCEPTION block just like any other error. However, since you're defining a custom error code (ranging from -20000 to -20999), you handle it using the WHEN OTHERS block or by checking the specific error code using SQLCODE.

1. PRAGMA EXCEPTION_INIT:

Purpose: This is used to associate an exception name with an Oracle error code. This way, when that specific error code is raised, you can handle it using the exception name.

Use Case: When you want to handle specific Oracle errors (like ORA-00001 for unique constraint violations), you can use PRAGMA EXCEPTION_INIT to make the code more readable.

The PRAGMA EXCEPTION_INIT helps you avoid catching specific Oracle errors in the WHEN OTHERS block by allowing you to handle them directly with a named exception.

In the following example, the PRAGMA EXCEPTION_INIT is used to associate the Oracle error ORA-00001 (unique constraint violation) with a named exception duplicate_value. Then, the exception is handled directly by referencing duplicate_value in the EXCEPTION block.

Example:

DECLARE
duplicate_value EXCEPTION;
PRAGMA EXCEPTION_INIT(duplicate_value, -00001); -- Associates ORA-00001 with duplicate_value exception
BEGIN
-- Code that might violate a unique constraint
INSERT INTO employees (employee_id, name) VALUES (1, 'John');
EXCEPTION
WHEN duplicate_value THEN
DBMS_OUTPUT.PUT_LINE('Duplicate value found. Please check your data.');
END;

Here, if the INSERT statement violates a unique constraint (error ORA-00001), it will be caught as duplicate_value, and the custom message will be displayed.

Summary:

RAISE_APPLICATION_ERROR is for creating custom errors.

PRAGMA EXCEPTION_INIT maps standard Oracle error codes to named exceptions for easier handling.

---

Let's deep dive into detail explanation

Certainly! I'll provide complete PL/SQL blocks for both scenarios: using RAISE_APPLICATION_ERROR to raise a custom error and using PRAGMA EXCEPTION_INIT to handle a specific Oracle error.

Scenario 1: Using RAISE_APPLICATION_ERROR

This block checks if a salary is less than 0. If it is, a custom error is raised.

DECLARE
v_salary NUMBER := -5000; -- Example of a negative salary
BEGIN
IF v_salary < 0 THEN
-- Raise a custom error with an error number and message
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative.');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary is valid.');
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Handle any unexpected errors
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Explanation:

If v_salary is negative, RAISE_APPLICATION_ERROR raises a custom error with a number -20001 and the message "Salary cannot be negative."

The EXCEPTION block will catch the error and display the error message.

Scenario 2: Using PRAGMA EXCEPTION_INIT

This block demonstrates how to handle a predefined Oracle error, such as a unique constraint violation (ORA-00001), using PRAGMA EXCEPTION_INIT.

DECLARE
duplicate_value EXCEPTION;
PRAGMA EXCEPTION_INIT(duplicate_value, -00001); -- Associate ORA-00001 with our custom exception

BEGIN
-- Attempt to insert a record that might violate a unique constraint
INSERT INTO employees (employee_id, name) VALUES (1, 'John');
INSERT INTO employees (employee_id, name) VALUES (1, 'Jane'); -- This will raise ORA-00001 because of duplicate employee_id

DBMS_OUTPUT.PUT_LINE('Record inserted successfully.');

EXCEPTION
WHEN duplicate_value THEN
-- Handle the specific case of duplicate values
DBMS_OUTPUT.PUT_LINE('Duplicate value found. Please check your data.');
WHEN OTHERS THEN
-- Handle any other unexpected errors
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation:

PRAGMA EXCEPTION_INIT(duplicate_value, -00001); maps the ORA-00001 error code (unique constraint violation) to the duplicate_value exception.

If the INSERT statement tries to insert a record with a duplicate employee_id, it raises ORA-00001, which is caught as duplicate_value.

The EXCEPTION block will then display a message: "Duplicate value found. Please check your data."

Key Takeaways:

RAISE_APPLICATION_ERROR allows you to define custom error messages and handle specific conditions within your PL/SQL code.

PRAGMA EXCEPTION_INIT simplifies handling known Oracle errors by mapping error codes to named exceptions.