🧩 How to Resolve ORA-01403: No Data Found in Oracle Database

🧩 Introduction

The ORA-01403: No Data Found error occurs when a query or PL/SQL block tries to fetch data, but no rows match the criteria.

This error is very common in PL/SQL programs where developers expect a single row from a SELECT INTO statement but none is returned.

Understanding why this happens and how to handle it is important for building robust Oracle applications.

---

Step 1: Reproduce the Error

The error typically appears in a PL/SQL block like this:

DECLARE

   v_name VARCHAR2(50);

BEGIN

   SELECT name INTO v_name

   FROM employees

   WHERE emp_id = 999; -- ID does not exist

END;

/

➡️ If no row with emp_id = 999 exists, Oracle will raise:

ORA-01403: no data found

---

Step 2: Understand Why It Happens

SELECT INTO must return exactly one row.

If zero rows are found → ORA-01403.

If more than one row is found → ORA-01422 (Too many rows).

This makes handling the error important in exception blocks.

---

Step 3: Handling the Error in PL/SQL

🔹 Option 1: Use Exception Handling

Wrap your code in an exception block to catch the error:

DECLARE

   v_name VARCHAR2(50);

BEGIN

   SELECT name INTO v_name

   FROM employees

   WHERE emp_id = 999;

   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);

EXCEPTION

   WHEN NO_DATA_FOUND THEN

      DBMS_OUTPUT.PUT_LINE('No employee found with this ID.');

END;

/

➡️ This prevents the program from crashing when no data exists.

---

🔹 Option 2: Use COUNT First

Check if the row exists before fetching:

DECLARE

   v_count NUMBER;

   v_name VARCHAR2(50);

BEGIN

   SELECT COUNT(*) INTO v_count

   FROM employees

   WHERE emp_id = 999;

   IF v_count > 0 THEN

      SELECT name INTO v_name

      FROM employees

      WHERE emp_id = 999;

      DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);

   ELSE

      DBMS_OUTPUT.PUT_LINE('No employee found with this ID.');

   END IF;

END;

/

---

🔹 Option 3: Use CURSORS

Cursors allow more flexibility when dealing with zero or multiple rows:

DECLARE

   CURSOR emp_cur IS

      SELECT name FROM employees WHERE emp_id = 999;

   v_name employees.name%TYPE;

BEGIN

   OPEN emp_cur;

   FETCH emp_cur INTO v_name;

   IF emp_cur%NOTFOUND THEN

      DBMS_OUTPUT.PUT_LINE('No employee found.');

   ELSE

      DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);

   END IF;

   CLOSE emp_cur;

END;

/

---

Step 4: Best Practices

Always anticipate missing data in PL/SQL.

Use EXCEPTION blocks to handle unexpected results.

For queries that may return multiple rows, avoid SELECT INTO and use CURSOR or BULK COLLECT.

Log missing data cases for debugging.

---

Conclusion

The ORA-01403: No Data Found error is not necessarily a problem with Oracle—it simply means no rows matched your query.

By using exception handling, checking for data existence before fetching, or applying cursors, you can gracefully handle this situation and keep your applications stable.

👉 Always code defensively when writing PL/SQL programs, especially with SELECT INTO.