🧩 How to Fix ORA-01403: No Data Found

 

🧩 Introduction

The ORA-01403 error occurs when a query in Oracle returns no rows, but the operation expects at least one row. This is common in PL/SQL blocks, functions, or procedures when using SELECT INTO statements. Understanding how to handle this error is essential for developers and DBAs to prevent runtime failures.

Step 1: Identify the Problematic Query

The error is triggered by a SELECT INTO statement that does not return any rows.

Example:

DECLARE

   v_name VARCHAR2(50);

BEGIN

   SELECT name INTO v_name

   FROM employees

   WHERE employee_id = 9999;

END;

If no employee has ID 9999, ORA-01403 will occur.

Step 2: Handle the Exception in PL/SQL

Use an EXCEPTION block to catch the error and handle it gracefully:

DECLARE

   v_name VARCHAR2(50);

BEGIN

   SELECT name INTO v_name

   FROM employees

   WHERE employee_id = 9999;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

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

END;

This prevents the program from failing and allows proper handling.

Step 3: Use Alternative Queries

Instead of SELECT INTO, consider using CURSOR or SELECT ... INTO with NVL or COALESCE functions to provide default values.

SELECT NVL(name, 'Unknown') INTO v_name

FROM employees

WHERE employee_id = 9999;

This ensures that a variable always has a value, avoiding the error.

Step 4: Verify Data Existence

Before performing operations that expect a row, check if data exists:

SELECT COUNT(*)

INTO v_count

FROM employees

WHERE employee_id = 9999;

IF v_count = 0 THEN

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

END IF;

This proactive check reduces the chance of ORA-01403 errors.

Step 5: Prevent Future Occurrences

Always handle exceptions when using SELECT INTO in PL/SQL.

Validate input parameters to ensure they correspond to existing data.

Consider using cursors or joins to retrieve optional data without causing runtime errors.

Conclusion

ORA-01403 (“No Data Found”) is a common PL/SQL error that occurs when a query returns no rows. By handling exceptions, validating input, and using alternative query techniques, developers and DBAs can prevent runtime failures and ensure robust, error-free database applications.