// Oracle error reference

ORA-01422: exact fetch returns more than requested rows

A SELECT INTO in PL/SQL returned more than one row. Narrow the query to one row, or fetch into a collection or loop.

Error code
ORA-01422
Category
PL/SQL
Error message
exact fetch returns more than requested number of rows

What it means

SELECT ... INTO must return exactly one row; when it returns several, PL/SQL raises the predefined exception TOO_MANY_ROWS (ORA-01422). The full 19c message is exact fetch returns more than requested number of rows.

Common causes

  • Looking up by a column that isn't unique.
  • A missing condition, so the query matches many rows.
  • Duplicate rows in data that was assumed unique.

How to fix it

  1. Filter on a unique key, or pick one row on purpose.

    SELECT id INTO l_id FROM customers
    WHERE email = p_email
    ORDER BY created_at DESC
    FETCH FIRST 1 ROW ONLY;
  2. If several rows are expected, fetch them into a collection or loop over a cursor.

    SELECT id BULK COLLECT INTO l_ids FROM customers WHERE email = p_email;

Related errors

More pl/sql errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.