How to Clear ORA-01403: No Data Found

To clear ORA-01403: no data found, find the PL/SQL statement that expected a row, then either correct the query or missing data, or explicitly handle an optional result. The most common cause is a single-row SELECT INTO that returns no rows. There is no database-wide “clear” command: the fix belongs in the failing lookup, its data, or its exception handling.

Use the symptom to choose where to investigate first.

Symptom	Likely Cause	Fix
A lookup fails for a particular ID	The required record is missing, or a filter excludes it	Test the lookup with the failing inputs and correct the data or condition
An optional lookup stops the application	The code does not handle an absent result	Add a narrowly scoped `NO_DATA_FOUND` handler
Saving or updating a record triggers the error	A trigger or called routine performs a failing lookup	Trace the full error stack to the underlying statement
The query works in your client but fails in the application	Different bind values, schema, transaction visibility, or application context	Reproduce the application’s actual execution context

What ORA-01403 Means

In PL/SQL, a non-bulk SELECT INTO expects exactly one row:

No rows: Oracle raises NO_DATA_FOUND.
One row: Oracle assigns its values to the target variables.
More than one row: Oracle raises TOO_MANY_ROWS, usually displayed as ORA-01422.

A row containing a NULL value still counts as a row. A missing email address in an existing user record is therefore different from a missing user record.

An ordinary SQL query that returns an empty result set normally does not raise this exception. Neither does an UPDATE or DELETE simply because it affects no rows. However, a function or trigger executed by those statements can raise it.

Not every occurrence comes from SELECT INTO. Accessing a nonexistent associative-array element or explicitly raising NO_DATA_FOUND can also produce the exception. That is why locating the actual failing line matters more than searching the application for any query that looks suspicious.

How to Fix ORA-01403

Step 1: Capture the Full Error and Locate the Failing Line

Confirm that the message includes ORA-01403: no data found.
Copy the entire error stack, including accompanying ORA-06512 entries and application messages.
Record the action that failed, the input values, and the relevant package, procedure, function, or trigger.
Inspect the source line identified by the stack, then follow any called routines.

An application may display only the final message while its logs contain the useful package name and line number. For a failed save, inspect both the application code and database triggers involved in that operation.

If you maintain the PL/SQL code, capture DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE at an exception boundary before re-raising the original exception. The backtrace helps identify where the exception originated, rather than merely where it was caught.

Use the application’s normal logger for persistent diagnostics. DBMS_OUTPUT is useful during interactive testing, but it is not a durable application log and may not be displayed by the caller.

Step 2: Run the Lookup With the Actual Inputs

Find the failing SELECT INTO.
Remove the INTO clause and run its query separately.
Supply the exact bind values used by the failing application request.
Check whether the result contains no rows, one row, or multiple rows.

For example, suppose your application uses a table named app_users, with columns named user_id and email. The diagnostic query would be:

SELECT email
FROM app_users
WHERE user_id = :user_id;

The application examples in this article are templates using those illustrative names. Substitute your actual table and column names, and supply the bind variable through your database client.

When a single-row SELECT INTO returns no rows, inspect what excluded the expected record:

Bind values: Check for the wrong ID, unexpected whitespace, incorrect data types, or missing input.
Null comparisons: Equality comparisons do not match NULL. If null is a valid search condition, the query needs deliberate null-handling logic.
Joins: An inner join can exclude an existing primary record when a related record is absent.
Additional filters: Status, tenant, date, and authorization conditions can remove an otherwise valid match.
Execution context: Confirm the schema, synonym resolution, and application context used by the caller.
Transaction visibility: Another session’s uncommitted changes are not visible to the failing session.

Oracle also treats an empty character string as NULL, which can turn an apparently supplied text input into a missing value.

If necessary, remove filters one at a time in a diagnostic copy to identify the exclusion. Do not deploy a weakened query that bypasses business or authorization rules merely to make it return something.

Step 3: Correct the Required Lookup or Handle an Optional Result

A missing required record needs a data or logic fix; a missing optional record needs an explicit application outcome.

Choose the branch that matches the application’s requirements.

If the row is required, repair the underlying cause. Correct the input, join, or filter, or create the missing business record through the supported application workflow. Do not insert a placeholder row solely to silence the error. If the required record genuinely does not exist, preserve the failure or translate it into a clear application error.
If the row is optional, handle its absence locally. Add an EXCEPTION block containing WHEN NO_DATA_FOUND THEN around that lookup, not around an entire transaction containing unrelated queries.
Assign a deliberate outcome and continue outside the inner block. Depending on the application, that may mean assigning a default, returning an explicit “not found” result, or skipping an optional action.

Here is an optional email lookup:

DECLARE
    l_email app_users.email%TYPE;
BEGIN
    BEGIN
        SELECT email
        INTO l_email
        FROM app_users
        WHERE user_id = :user_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            l_email := NULL;
    END;
    IF l_email IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('No email available; notification skipped.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Email available; notification can proceed.');
    END IF;
END;
/

This example deliberately treats a missing user and an existing user with a null email as the same outcome. If the application must distinguish them, record a separate “user found” flag or return a structured status.

The nested block is important. Once its exception handler finishes, execution continues after that block; it does not resume at the failed statement or execute statements skipped inside the block.

For SQL*Plus or SQLcl, enable output before running the example:

SET SERVEROUTPUT ON

The final slash in the PL/SQL example is a SQL*Plus-style execution delimiter. Other database clients may execute the block directly and provide their own DBMS Output control.

Avoid these shortcuts:

A broad WHEN OTHERS handler that silently ignores failures.
A handler that prints a message but lets the caller assume required work succeeded.
An arbitrary default that looks like valid business data.
A SQL%NOTFOUND check immediately after a single-row SELECT INTO. The exception occurs before that check can execute.

Step 4: Retest Through the Original Caller

Run the operation with an input that has a valid matching record.
Run it with the input that originally failed.
Verify the intended missing-record behavior: a clear failure for required data, or a controlled result for optional data.
Execute the original application action, not just the isolated SQL.
Check the resulting data, application response, and logs before deploying the change.

If fixing the filter now produces multiple matches, resolve the uniqueness problem or choose a multiple-row processing method. Do not hide it by selecting an arbitrary first row.

Keep normal transaction boundaries intact. Adding a commit or restart does not make an incorrect lookup correct.

Handle Cursors and Other No-Row Patterns

Different PL/SQL retrieval methods have different empty-result behavior. Use the method that matches the number of rows the application actually expects.

Explicit Cursors

An explicit cursor’s FETCH does not automatically raise NO_DATA_FOUND when it finds no row. Instead, check the cursor’s %NOTFOUND attribute immediately after the fetch and take the missing-result path before using the fetched variable.

DECLARE
    CURSOR c_email IS
        SELECT email
        FROM app_users
        WHERE user_id = :user_id;
    l_email app_users.email%TYPE;
BEGIN
    OPEN c_email;
    FETCH c_email INTO l_email;
    IF c_email%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No matching user.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Matching user found.');
    END IF;
    CLOSE c_email;
EXCEPTION
    WHEN OTHERS THEN
        IF c_email%ISOPEN THEN
            CLOSE c_email;
        END IF;
        RAISE;
END;
/

This example assumes the lookup should match at most one user. It closes the cursor on normal completion and closes it before re-raising an unexpected exception.

Test both a matching and a missing input. The missing input should take the %NOTFOUND branch without using the fetched value.

Cursor FOR Loops and Bulk Collection

If the application should process every matching row, a cursor FOR loop is usually clearer than a single-row lookup:

BEGIN
    FOR r IN (
        SELECT user_id, email
        FROM app_users
        WHERE email IS NOT NULL
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Processing a matching user.');
    END LOOP;
END;
/

An empty result means the loop body does not run. PL/SQL manages opening, fetching, and closing the cursor.

Replace the demonstration output with the intended per-row work, then test with an empty result and with matching records. If an empty result requires a notification or failure, track whether the loop processed anything and handle that outcome explicitly.

Similarly, SELECT BULK COLLECT INTO can return an empty collection without raising NO_DATA_FOUND. Check the collection’s count before accessing elements.

Aggregates, NVL, and Count Checks

An aggregate query without GROUP BY, such as COUNT(*), returns a result even when no input rows match. Other aggregates, such as MAX, generally return NULL for an empty input.

That does not make aggregates a universal repair:

Adding GROUP BY can produce no groups and therefore no result rows.
A HAVING condition can filter out the aggregate result.
Replacing a lookup with MAX can hide duplicate records by selecting one value.
Checking COUNT(*) before performing a separate lookup introduces a race: another session can change the data between statements.

Also, wrapping a selected column in NVL only replaces a null value in a row that exists. It does not create a row when the query matches nothing.

Use aggregation when the requested result is genuinely an aggregate, not simply to suppress an exception.

Fix Application and Trigger Failures

Oracle Forms and Database Triggers

A save operation can fail because an update trigger, Forms trigger, or called package performs an unsuccessful lookup. The screen action itself does not reveal which lookup failed.

Capture the complete message and identify the named trigger or routine. Then inspect the statement that raised the exception and decide whether its missing result is valid.

If the stack also contains ORA-04091, there is a separate mutating-table problem to investigate. Adding a no-data handler does not fix that trigger restriction. Likewise, an FRM-40735 message identifies an unhandled trigger exception; its accompanying Oracle error supplies the underlying cause.

After changing custom code, compile the affected object and rerun the original save in a test environment. Confirm both the saved record and any related trigger-generated changes.

Oracle EBS, Period Close, and Balance Adjustments

In a packaged application, the error may reflect missing setup, an unexpected data relationship, custom code, or a product defect. The Oracle message alone cannot identify the correct configuration change or data repair.

Collect:

The application product and release.
The exact screen, operation, or concurrent request.
The complete error stack and relevant request log.
The affected business context, without exposing sensitive information.
Whether the problem affects every record or only particular records.
Any recent configuration or customization changes.

Use that evidence to identify the failing lookup through approved diagnostics or product support. Do not directly edit application tables or modify seeded packages to bypass the message.

Apply the supported configuration change, patch, or approved data repair in a test environment first. Then rerun the complete business operation and verify its final status and resulting data, not merely that the popup disappeared.

Verify the Fix and Prevent Recurrence

A successful fix produces the correct business outcome, not just a quieter log.

Before closing the issue, confirm:

Valid input still works. The expected record is retrieved and processed.
Missing required data fails clearly. The caller cannot mistake an incomplete operation for success.
Missing optional data follows the intended branch. The fallback does not invent valid-looking information.
Null values remain distinguishable where necessary. “Record missing” and “field empty” are not accidentally conflated.
Diagnostics remain useful. Unexpected failures retain their stack and relevant context.
The original workflow completes correctly. Associated changes and transaction behavior remain intact.

To prevent recurrence, define each lookup’s contract: required single row, optional single row, or multiple rows. Enforce genuine uniqueness and relationship requirements with appropriate database constraints, and include missing-record behavior in automated tests.

Once those checks pass, deploy through the normal release process and monitor the originally failing operation.

Frequently Asked Questions

What Does ORA-06512 at Line 1 Mean?

ORA-06512 identifies a PL/SQL stack location; it is not the underlying cause of the failure. “At line 1” may refer to a one-line anonymous block that calls a procedure, rather than the statement inside that procedure that failed. Capture the complete stack and error backtrace to locate the originating source line.

Why Does GET_EMAIL Raise NO_DATA_FOUND?

A function named GET_EMAIL can raise this exception when its implementation performs a lookup that finds no matching record. The name alone does not identify the package or implementation, so inspect the function’s source and actual input values. Decide separately whether an absent user and an existing user without an email address should return the same result.

How Do I Fix KVS-00016 or a CUSH Message With ORA-01403?

KVS-00016 or CUSH messages accompanied by ORA-01403 need an application-specific diagnosis; the Oracle code alone does not identify which product setting or record is wrong. Capture the product name, complete surrounding message, and failing operation, then identify the underlying routine through its logs or support diagnostics. Do not apply a data patch intended for a different application merely because the Oracle error matches.

ORA-00933 is a SQL syntax or statement-structure error, not a missing-result exception. Check the exact SQL submitted for unsupported syntax, misplaced clauses, or a statement terminator incorrectly included by a client API. Adding a NO_DATA_FOUND handler will not repair malformed SQL.

How should you investigate ORA-01403?

Review the SELECT INTO statement and check the table name, column names, and WHERE conditions. ORA-01403 is raised when that statement returns no rows, so confirm that the expected matching data exists.