ORA-01403: no data found

In PL/SQL, a SELECT … INTO returned no row (or code read an associative-array element that does not exist). Handle the NO_DATA_FOUND exception, or write the query so that “no row” is a normal result.

What it means

SELECT … INTO must return exactly one row. With zero rows PL/SQL raises the predefined exception NO_DATA_FOUND (ORA-01403). It is also raised when you read an index-by table element that was never assigned. Note that if a function called from SQL raises it, the SQL statement silently gets NULL instead of an error.

Common causes

The WHERE clause matched no row — wrong key, deleted data, or a different schema.
Data visible to another session is not committed yet.
Reading my_table(i) of a collection for an index that was never set.
Case or trailing-space differences in a string comparison.

How to fix it

Catch it where “not found” is expected: EXCEPTION WHEN NO_DATA_FOUND THEN ….
Or avoid it: an aggregate such as MAX(col) always returns one row (NULL when nothing matches).
Use a cursor loop if zero, one or many rows are all valid.
For collections, check my_table.EXISTS(i) before reading.

Example

Handle the empty case explicitly:

BEGIN
  SELECT salary INTO l_salary FROM employees WHERE id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    l_salary := NULL;
END;

Or never raise it:

SELECT MAX(salary) INTO l_salary FROM employees WHERE id = p_id;

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-01403. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.