ORA-01422: exact fetch returned more than the requested number of rows
ORA-01422: exact fetch returned more than the requested number of rows (1)A PL/SQL SELECT INTO matched more than one row.
Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-14
SELECT ... INTO in PL/SQL must return exactly one row. When the WHERE clause matches two or more rows, Oracle raises ORA-01422 (TOO_MANY_ROWS). The (1) at the end of the 23ai message is the number of rows the statement expected.
Run the same SELECT on its own, without INTO, and look at the rows it returns. They tell you whether the WHERE clause is too loose or the data itself is duplicated.
Causes and fixes
1Looking up by a column that is not unique, such as a name
The block reads a salary WHERE name = 'Kim'. Two employees are named Kim (101 in dept 10, 103 in dept 20), so the fetch returned 2 rows.
Look up by the key instead: WHERE emp_id = 103 returned one row and printed salary=3900.
Setup
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(30), dept_id NUMBER, salary NUMBER);
INSERT INTO emp VALUES (101, 'Kim', 10, 5000);
INSERT INTO emp VALUES (102, 'Lee', 10, 4200);
INSERT INTO emp VALUES (103, 'Kim', 20, 3900);
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SET SERVEROUTPUT ON
DECLARE
v_salary emp.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM emp WHERE name = 'Kim';
DBMS_OUTPUT.PUT_LINE('salary=' || v_salary);
END;
/DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returned more than the requested number of rows (1)
Help: https://docs.oracle.com/error-help/db/ora-01422/SELECT emp_id, name, dept_id FROM emp WHERE name = 'Kim' ORDER BY emp_id; EMP_ID NAME DEPT_ID
---------- ------------------------------ ----------
101 Kim 10
103 Kim 20
2 rows selected.If the screen only has a name, let the user pick from a list and pass the ID to the PL/SQL code.
SET SERVEROUTPUT ON
DECLARE
v_salary emp.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM emp WHERE emp_id = 103;
DBMS_OUTPUT.PUT_LINE('salary=' || v_salary);
END;
/salary=3900
PL/SQL procedure successfully completed.2A history table has several rows for the same key
price_hist keeps every price change. Item 1 has 1000 from 2025-01-01 and 1200 from 2026-01-01, so WHERE item_id = 1 matched 2 rows.
State which row you want: ORDER BY start_date DESC FETCH FIRST 1 ROW ONLY returned the latest one, price=1200.
Setup
CREATE TABLE price_hist (item_id NUMBER, price NUMBER, start_date DATE);
INSERT INTO price_hist VALUES (1, 1000, DATE '2025-01-01');
INSERT INTO price_hist VALUES (1, 1200, DATE '2026-01-01');
INSERT INTO price_hist VALUES (2, 500, DATE '2025-01-01');
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SET SERVEROUTPUT ON
DECLARE
v_price NUMBER;
BEGIN
SELECT price INTO v_price FROM price_hist WHERE item_id = 1;
DBMS_OUTPUT.PUT_LINE('price=' || v_price);
END;
/DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returned more than the requested number of rows (1)
Help: https://docs.oracle.com/error-help/db/ora-01422/SELECT item_id, price, start_date FROM price_hist WHERE item_id = 1 ORDER BY start_date; ITEM_ID PRICE START_DATE
---------- ---------- ------------------
1 1000 01-JAN-25
1 1200 01-JAN-26
2 rows selected.Code like this often works for months and breaks the first time a price changes. Any table with a start date can have more than one row per key.
SET SERVEROUTPUT ON
DECLARE
v_price NUMBER;
BEGIN
SELECT price INTO v_price FROM price_hist
WHERE item_id = 1
ORDER BY start_date DESC
FETCH FIRST 1 ROW ONLY;
DBMS_OUTPUT.PUT_LINE('price=' || v_price);
END;
/price=1200
PL/SQL procedure successfully completed.3The same data was inserted twice and there is no unique constraint
dept_code has no unique key, and D10 Sales was inserted twice (for example, a load that ran twice). The query is correct; the data is wrong.
GROUP BY code HAVING COUNT(*) > 1 found D10 with 2 rows. Deleting all but MIN(ROWID) removed 1 row, and ALTER TABLE ... ADD CONSTRAINT dept_code_uk UNIQUE (code) stops it from happening again. The block then printed D10=Sales.
Setup
CREATE TABLE dept_code (code VARCHAR2(10), dept_name VARCHAR2(30));
INSERT INTO dept_code VALUES ('D10', 'Sales');
INSERT INTO dept_code VALUES ('D20', 'Finance');
INSERT INTO dept_code VALUES ('D10', 'Sales');
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SET SERVEROUTPUT ON
DECLARE
v_name dept_code.dept_name%TYPE;
BEGIN
SELECT dept_name INTO v_name FROM dept_code WHERE code = 'D10';
DBMS_OUTPUT.PUT_LINE('D10=' || v_name);
END;
/DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returned more than the requested number of rows (1)
Help: https://docs.oracle.com/error-help/db/ora-01422/SELECT code, COUNT(*) AS cnt FROM dept_code GROUP BY code HAVING COUNT(*) > 1;CODE CNT
---------- ----------
D10 2
1 row selected.DELETE FROM dept_code
WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM dept_code GROUP BY code);
COMMIT;
ALTER TABLE dept_code ADD CONSTRAINT dept_code_uk UNIQUE (code);1 row deleted.
Commit complete.
Table altered.Check what else depends on the duplicate rows before deleting them. Here both rows had the same values.
SET SERVEROUTPUT ON
DECLARE
v_name dept_code.dept_name%TYPE;
BEGIN
SELECT dept_name INTO v_name FROM dept_code WHERE code = 'D10';
DBMS_OUTPUT.PUT_LINE('D10=' || v_name);
END;
/D10=Sales
PL/SQL procedure successfully completed.FAQ
What does "exact fetch returns more than requested number of rows" mean?
A PL/SQL SELECT INTO matched more than one row. It can only hold one. Run the SELECT without INTO to see the extra rows.
How do I get only one row in a PL/SQL SELECT INTO?
Make the WHERE clause match one row, ideally by the key. If you really want one of several, say which: in this lab ORDER BY start_date DESC FETCH FIRST 1 ROW ONLY picked the latest price.
How do I find duplicate rows that cause ORA-01422?
SELECT code, COUNT(*) FROM t GROUP BY code HAVING COUNT(*) > 1. Then remove the extra rows and add a unique constraint.
What is the difference between ORA-01422 and ORA-01427?
ORA-01422 is a PL/SQL SELECT INTO returning several rows. ORA-01427 is a subquery used as a single value returning several rows.
How we verify
Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.
lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free