How to Resolve ORA-01403: No Data Found

Introduction
Understanding the Cause
Step-by-Step Solutions
Examples
Conclusion

Introduction

The ORA-01403 error is a common issue in Oracle databases, indicating that no data was found for a specified query. The error message typically reads:
ORA-01403: no data found
This error can occur during a SELECT INTO statement when the query does not return any rows. It is crucial to understand the cause of this error and how to resolve it to ensure smooth database operations.

Understanding the Cause

The ORA-01403 error occurs when a SELECT INTO statement does not return any rows. This can happen due to several reasons:
  • No Matching Data: The query criteria do not match any rows in the database.
  • Incorrect Query: The query may be incorrectly formulated, leading to no results.
  • Data Inconsistency: The data in the database may have changed, causing previously valid queries to return no results.

Step-by-Step Solutions

To resolve ORA-01403, follow these troubleshooting steps:

1. Check the Query Criteria

Ensure that the criteria specified in your query are correct and that there is data in the database that matches these criteria.

2. Use Exception Handling

Incorporate exception handling in your PL/SQL block to manage the situation when no data is found.


BEGIN
    SELECT column_name
    INTO variable_name
    FROM table_name
    WHERE condition;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Handle the exception
        DBMS_OUTPUT.PUT_LINE('No data found.');
END;
        

3. Verify Data Integrity

Ensure that the data in your database is consistent and that there are no missing or unexpected values.

4. Modify the Query

Modify the query to include conditions that are more likely to return results. For example, if you are searching for a specific record, ensure that the criteria match existing records.

Examples

Example 1: No Matching Data

Consider the following SQL query:


BEGIN
    SELECT first_name
    INTO v_first_name
    FROM employees
    WHERE employee_id = 9999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.');
END;
        

In this example, if there is no employee with ID 9999, the NO_DATA_FOUND exception is raised, and a message is printed.

Example 2: Incorrect Query

Consider the following SQL query:


BEGIN
    SELECT first_name
    INTO v_first_name
    FROM employees
    WHERE first_name = 'John'
    AND last_name = 'Doe';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with the given name.');
END;
        

If there is no employee with the first name 'John' and last name 'Doe', the NO_DATA_FOUND exception is raised, and a message is printed.

Example 3: Data Inconsistency

Consider the following scenario where the data in the database has changed:


BEGIN
    SELECT first_name
    INTO v_first_name
    FROM employees
    WHERE employee_id = 100;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.');
END;
        

If the employee with ID 100 has been deleted or the ID has been changed, the NO_DATA_FOUND exception is raised, and a message is printed.

Conclusion

ORA-01403: No Data Found is an Oracle error that occurs when a SELECT INTO statement does not return any rows. By understanding its causes and following the provided solutions, database administrators and developers can effectively troubleshoot and resolve this error, ensuring smooth database operations. Regularly reviewing query criteria, incorporating exception handling, verifying data integrity, and modifying queries are essential practices to prevent this issue from recurring.

Related content



Rate Your Experience

: 89 : 1


Last updated in July, 2024

Online Tests
Read more

Cloud Technology
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Quick Access