PL/SQL RAISE Exceptions

PL/SQL stands for Procedural Language Extension to the Structured Query Language and it is designed specifically for Oracle databases. It extends Structured Query Language (SQL) capabilities by allowing the creation of stored procedures, functions, and triggers. It is a block-structured language that combines SQL with the procedural features of programming languages. In this article, we will learn about RAISE Exception in PL/SQL with its syntax, types, and examples.

PL/SQL Raise Exception is a feature that helps us to handle errors in a structured and efficient manner. Exceptions in PL/SQL are used to manage unexpected situations and ensure the integrity of the database. There are three ways to Raise an exception in PL/SQL.

1. User-Defined Exception.
2. Internally Defined Exception.
3. Current Exception.

Syntax:

DECLARE
   custom_exception EXCEPTION;
BEGIN
   -- Some condition that may warrant raising a user-defined exception
   IF some_condition THEN
      -- Raise a user-defined exception
      RAISE custom_exception;
   END IF;

   -- Rest of the code...

EXCEPTION
   WHEN custom_exception THEN
      -- Handle the user-defined exception
      DBMS_OUTPUT.PUT_LINE('Custom exception handled!');
END;

Explanation:

• if some_condition is true then the RAISE custom_exception statement will be executed and make happen the user-defined exception custom_exception to be raised.
  
• The WHEN custom_exception THEN block in the EXCEPTION section will catch and handle this exception.

It is a type of exception that is defined by a developer to handle specific error conditions or business rules in their PL/SQL code. we can declare a user-defined exception using the EXCEPTION keyword and we can raise it using the RAISE statement.

Example:

DECLARE
    custom_exception EXCEPTION;
BEGIN
    IF true THEN
        -- If a specific condition is met, raise the user-defined exception
        RAISE custom_exception;
    END IF;
EXCEPTION
    WHEN custom_exception THEN
        DBMS_OUTPUT.PUT_LINE('Custom exception handled!');
END;

Output:

Statement processed.
Custom exception handled!

Explanation: In the above query we have declares a custom exception custom_exception. If a condition (in this case, always true for demonstration purposes) is met then it raises the custom_exception. The EXCEPTION block catches and handles the custom_exception, printing ‘Custom exception handled!‘ using DBMS_OUTPUT.PUT_LINE.

It is type of exception that are predefined by the PL/SQL runtime environment to handle common error conditions which occur during program execution. It includes exception such as NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, DUP_VAL_ON_INDEX, STORAGE_ERROR etc.

Example:

DECLARE
    result NUMBER;
BEGIN
    -- Some computation that might cause an internally defined exception
    result := 10 / 0; -- This will raise ZERO_DIVIDE exception
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Cannot divide by zero!');
END;

Output:

Statement processed.
Cannot divide by zero!

Explanation: In the above query we computes a division operation (10 / 0) which causes a division by zero error and leading to the ZERO_DIVIDE exception being raised. The EXCEPTION block catches and handles this exception by printing ‘Cannot divide by zero!‘ using DBMS_OUTPUT.PUT_LINE.

It is a type of exception that is currently being handled in the PL/SQL block. It is useful in nested blocks where an exception might be raised in an inner block and you want to reference that specific exception in an outer block. SQLERRM function is used to refer current exception.

Example:

DECLARE
    custom_exception EXCEPTION;
BEGIN
    BEGIN
        -- Some operation that might raise an exception
        RAISE custom_exception;
    EXCEPTION
        WHEN OTHERS THEN
            -- Handle the exception and reference the current exception
            DBMS_OUTPUT.PUT_LINE('Exception handled: ' || SQLERRM);
    END;
END;

Output:

Statement processed.
Exception handled: User-Defined Exception

Explanation: In the above query we demonstrates nested exception handling. It raises a user-defined exception (custom_exception) within an inner block. The EXCEPTION block in the outer block catches any exceptions (including the custom_exception) using WHEN OTHERS and prints the error message (SQLERRM) to the console.

The below example is demonstrating the use of user-defined exceptions in a real-world scenario. It illustrates how to create a table, a PL/SQL procedure, and handle exceptions related to a specific business rule (salary limit).

Step 1: Create a employee table

CREATE TABLE employee (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    emp_salary NUMBER
);

Step 2: Create a Procedure with an Exception

CREATE OR REPLACE PROCEDURE insert_employee(
    p_emp_id NUMBER,
    p_emp_name VARCHAR2,
    p_emp_salary NUMBER
)
IS
    emp_salary_limit EXCEPTION;
BEGIN
    -- Check if the salary is within the allowed limit
    IF p_emp_salary > 100000 THEN
        -- If not, raise a user-defined exception
        RAISE emp_salary_limit;
    ELSE
        -- Insert the employee details into the table
        INSERT INTO employee(emp_id, emp_name, emp_salary)
        VALUES (p_emp_id, p_emp_name, p_emp_salary);

        DBMS_OUTPUT.PUT_LINE('Employee inserted successfully.');
    END IF;
EXCEPTION
    WHEN emp_salary_limit THEN
        -- Handle the user-defined exception
        DBMS_OUTPUT.PUT_LINE('Error: Employee salary exceeds the allowed limit.');
END;

This procedure will check if the p_emp_salary is greater then 100000 then it will raise a user-defined exception(emp_salary_limit) else it will insert a new record into a employee table.

Step 3: Run the Procedure

DECLARE
    emp_id_param NUMBER := 101;
    emp_name_param VARCHAR2(50) := 'John Doe';
    emp_salary_param NUMBER := 120000;
BEGIN
    insert_employee(emp_id_param, emp_name_param, emp_salary_param);
END;

Output:

Statement processed. high salary
Error: Employee salary exceeds the allowed limit.

Now trying to insert new record with low salary.

DECLARE
    emp_id_param NUMBER := 101;
    emp_name_param VARCHAR2(50) := 'John Doe';
    emp_salary_param NUMBER := 10000;
BEGIN
    insert_employee(emp_id_param, emp_name_param, emp_salary_param);
END;

Output:

Statement processed.
Employee inserted successfully.

Pl/SQL is a Procedural Language that is used to write program blocks, procedures, functions, cursors, triggers for databases. It provides a Raise exception feature that is used to handle errors in a structured and efficient manner. You can use predefined exceptions, custom exceptions, or referring to the current exception to ensure the code handles unexpected situations.