Oracle Exception No Data Found: How to handle ora 01403

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