Examples of PL/SQL Raise Exception

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 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.