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

  1. Looking up by a column that is not unique, such as a name
  2. A history table has several rows for the same key
  3. The same data was inserted twice and there is no unique constraint

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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
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;
/
OUTPUT
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/
Checkrun as lab_a
SELECT emp_id, name, dept_id FROM emp WHERE name = 'Kim' ORDER BY emp_id;
OUTPUT
    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.

After the fixrun as lab_a
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;
/
OUTPUT
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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
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;
/
OUTPUT
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/
Checkrun as lab_a
SELECT item_id, price, start_date FROM price_hist WHERE item_id = 1 ORDER BY start_date;
OUTPUT
   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.

After the fixrun as lab_a
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;
/
OUTPUT
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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
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;
/
OUTPUT
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/
Checkrun as lab_a
SELECT code, COUNT(*) AS cnt FROM dept_code GROUP BY code HAVING COUNT(*) > 1;
OUTPUT
CODE              CNT
---------- ----------
D10                 2

1 row selected.
Fixrun as lab_a
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);
OUTPUT
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.

After the fixrun as lab_a
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;
/
OUTPUT
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

Related errors