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
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
-
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;
-
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
ORA-01403no data foundORA-01427single-row subquery returns more than one row512SQL Server: Subquery returned more than 1 value1242MySQL: Subquery returns more than 1 row
More pl/sql errors
ORA-06502PL/SQL: numeric or value errorORA-06512at line (PL/SQL error stack)ORA-06550PL/SQL compilation error at line, columnORA-06508PL/SQL: could not find program unit being calledORA-06503PL/SQL: function returned without valueORA-04091table is mutating, trigger may not see itORA-04088error during execution of triggerORA-04068existing state of packages has been discarded
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.