PL/SQL RAISE Exceptions (original) (raw)

Last Updated : 21 Oct, 2024

**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. PL/SQL provides a structured approach for raising and handling exceptions. With RAISE exceptions, you can not only detect issues but also respond to them appropriately, whether by logging errors, displaying meaningful messages, or executing alternative logic.

Let us get a better understanding of the concept of Exceptions in PL/SQL from this article.

What Are Exceptions in PL/SQL?

Exceptions in PL/SQL are mechanisms used to manage runtime errors and unexpected situations that may occur during the execution of a PL/SQL block. By **raising exceptions, you can gracefully handle errors, ensure data integrity, and provide meaningful error messages to users or developers. There are three main types of exceptions in PL/SQL:

Table of Content

How to Raise Exceptions in PL/SQL?

PL/SQL provides structured ways to raise exceptions within code blocks. This process involves using the **RAISE statement to trigger an error, followed by catching it in an **EXCEPTION block.

**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:

1. User-Defined 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.

2. Internally Defined Exception

Internally defined exceptions are pre-built into PL/SQL to handle common error conditions such as division by zero, no data found, or too many rows fetched. Some of the commonly used internal exceptions include:

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

3. Current Exception

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.

Example: Using Exceptions in PL/SQL

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: Execute the Procedure

1. Attempt to Insert an Employee with a High Salary

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

**2. Attempt to Insert an Employee with a 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.

Important Points About PL/SQL Exceptions