ORA-01422: exact fetch returns more than requested number of rows

In PL/SQL, a SELECT … INTO returned more than one row. Make the WHERE clause unique, pick one row deliberately, or fetch into a collection or loop.

What it means

SELECT … INTO into scalar variables can hold only one row. When the query returns two or more, PL/SQL raises the predefined exception TOO_MANY_ROWS (ORA-01422). It often appears after data changes make a previously unique lookup ambiguous.

Common causes

The WHERE clause is not on a unique key.
Duplicate data, for example after a load without a unique constraint.
A missing join condition multiplying rows.
A lookup that was unique in test data but not in production.

How to fix it

Filter on a unique or primary key.
If several rows are valid, choose one on purpose: ORDER BY … FETCH FIRST 1 ROW ONLY (12c and later).
Use BULK COLLECT INTO a collection or a cursor loop when you need all rows.
Add a unique constraint if duplicates should never exist.

Example

Pick the latest row deliberately (12c and later):

SELECT status INTO l_status
  FROM order_history
 WHERE order_id = p_id
 ORDER BY changed_at DESC
 FETCH FIRST 1 ROW ONLY;

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-01422. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.