ORA-01403 No Data Found Error

The OERR utility provides this information for the ORA error.

    01403, 00000, “no data found”
    No data was found from the objects.
    There was no data from the objects which may be due to end of fetch.

Question: What is the cause of the “ORA-01403 no data found error”? What can be done to solve this?

    DECLARE
    v_name VARCHAR2(20);
    BEGIN
    SELECT product_name
    INTO v_name
    FROM product
    WHERE product_id = &product_id;
    DBMS_OUTPUT.PUT_LINE(v_name);
    END;
    SQL> /
    Enter value for product_id: 74845
    old 7: where product_id = &product_id;
    new 7: where product_id = 74845;

    declare
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 4

Answer: There are multiple reasons you can get the “ORA-01403 no data found” error. The most common reasons are:

• A SQL SELECT INTO statement inside a PLSQL block does not return a row. You can use the WHEN NO_DATA_FOUND exception to avoid returning the ORA-01403 error to the end user.

• If you are using UTL_FILE this may be the reason. There was an attempt to read past the end of file when using the UTL_FILE package. To resolve this, fix the code to stop reading prior to end of the file.

• If you are using LOGICAL STANDBY this may be the reason. Previous use of Skip rule on DML operations causing a data mismatch. In this case simply skip the transaction and restart the apply process.

Metalink Notes for ORA-01403
• Master Note for Troubleshooting Streams Apply Errors ORA-1403, ORA-26787 or ORA-26786,Conflict Resolution (Doc ID 265201.1)

• Handling ORA-1403 ora-12801 on logical standby apply (Doc ID 1178284.1)

Related OraTips

What is the purpose of the NOFILENAMECHECK parameter? Learn why the NOFILENAMECHECK parameter is used.

Oracle Database Server Security Checklist – Security hardening of a database is an elaborate task requiring a detailed review of the environment. Read more on our Oracle Database Server Security Checklist

Other Articles

Oracle 12c Database: New Features The Oracle 12c Database has the most new features ever released with any version of any database. As you might have guessed from it’s name, the new version is more focused on Cloud Computing.

Oracle 12c RAC: New Features Oracle has come a long way from its inception of the 9i RAC database to its current 12c version. I remember the days when we had to deal with RAC related issues, ranging from performance to stability which gradually improved as this product matured. Learn more about the new feature now available with RAC.


Note: This Oracle documentation was created for reference for use by Vitalsofttech DBA’s. If you have any questions please post by clicking on the ASK A QUESTION link above.

Leave a Reply

Your email address will not be published.