The Oracle Exception No Data Found in PL/SQL is helping to handle no data return from one of the below-described scenarios. For example, if you SELECT INTO statement does not retrieve any data, then this exception is a valid match for handling those scenarios because the query expects some matching records but does not find any matching records.
This is very common for most of the quarries but if you do not handle code in the correct way to check empty results then you have to Oracle Exception No Data Found. You are free to catch this error and handle it accordingly. See our easy-to-learn examples for more information.
The first simple explanation for how the “NO_DATA_FOUND” exception works and how to handle it:
1. When no data found an exception in Oracle?
The “NO_DATA_FOUND” exception raise on below situations:
- A SELECT INTO statement retrieves data into a variable but a matching record is not found.
- A cursor executes to retrieve data (e.g., FETCH or FETCH INTO) for looping or setting value into a variable from a cursor, but there are no more rows to fetch.
- When using explicit cursors, a cursor can open but then fetch the data there can be empty rows.
2. How to Handle it?
ora 01403 no data found exception is used to change your behavior once raise the exception. You can specify the new flow if the NO_DATA_FOUND error is raised. Let’s see a small example first.
--
BEGIN
-- Your SQL query or cursor execution
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Code to handle the exception goes here
END;
--In this syntax, the block between BEGIN and EXCEPTION is used to write data retrieving code. It can be a select statement or a Cursor.
Once an exception occurs it comes to the Exception section and checks whether there are any matching error codes available in the list. If the error code matches, then the error can be handled accordingly. You are free to add a function, procedure, code segment, or logging message inside this exception block.
3. Oracle No Data Found Exception Example
This example shows the basic syntax and way of handling the NO_DATA_FOUND Error Code.
Fetch data using Select Statement.
--
DECLARE
employee_id_ NUMBER := 101;
employee_name_ VARCHAR2(100);
BEGIN
SELECT employee_name
INTO employee_name_
FROM employees
WHERE employee_id = employee_id_;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name_);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || employee_id_ || ' not found.');
END;
/
--In this example:
- Here we filter employee names using employee_id. And then print the employee’s name.
- If the query returns a row, we display the employee’s name.
- If there is an exception called “NO_DATA_FOUND” then that will catch the condition.
- The EXCEPTION block handles the exception because there are no data found by the select statement. Therefore logging message saying that.
Using Cursors in Oracle PL/SQL:
This example illustrates how to handle the cursor when a row is not returned by the cursor
Your normal cursor comes between a BEGIN...EXCEPTION block. If the cursor fetch operation Return NO_DATA_FOUND exception when fetching the data we can handle it using in the EXEPTION section.
--
DECLARE
CURSOR all_employee IS
SELECT employee_id
FROM employees;
emp_id_ employees.employee_id%TYPE;
BEGIN
OPEN c_employee;
LOOP
BEGIN
FETCH all_employee INTO emp_id_;
EXIT WHEN all_employee%NOTFOUND;
-- Process each employee record here
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Handle the exception when no data is found
DBMS_OUTPUT.PUT_LINE('No more data found.');
END; END LOOP;
CLOSE all_employee;
END;
/
--EXIT when no data Found in Oracle
--
DECLARE
employee_id_ NUMBER := 101;
employee_name_ VARCHAR2(100);
BEGIN
SELECT employee_name
INTO employee_name_
FROM employees
WHERE employee_id = employee_id_;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name_);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIt;
END;
/
--If you hope to exit from execution once found and no data found exception just add the exit keyword in when the condition block is related to your exception.
4. Importance of Handle no data found Exception in Oracle
Handling exceptions in a code is more important in professional writing because errors can break the flow of a program. If it raises an error it should be handled in a logical way to identify by the runner.
NO_DATA_FOUND is one of the very common errors that can be expected all the time when retrieving data from a database.
in summary, the “NO_DATA_FOUND” is a fundamental part of Oracle PL/SQL error handling. By handling it effectively, you can improve the reliability and user-friendliness of your database applications.
