🧩 How to Fix ORA-01422: Exact Fetch Returns More Than Requested Number of Rows

🧩 Introduction

The ORA-01422: Exact fetch returns more than requested number of rows error occurs when you use a SELECT INTO statement in PL/SQL, but the query returns more than one row.

Unlike ORA-01403 (No Data Found), this error means that your query matched multiple rows, while Oracle expected exactly one.

This error is common in cases where the WHERE clause is not restrictive enough. Let’s explore why it happens and how to solve it.

---

Step 1: Reproduce the Error

Example:

DECLARE

   v_name VARCHAR2(50);

BEGIN

   SELECT name INTO v_name

   FROM employees

   WHERE department_id = 10; -- Returns multiple employees

END;

/

➡️ If more than one employee exists in department 10, Oracle will raise:

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

---

Step 2: Why It Happens

SELECT INTO expects only one row.

If zero rows → ORA-01403 (No Data Found).

If one row → Works fine.

If more than one row → ORA-01422.

---

Step 3: Solutions

🔹 Option 1: Restrict the Query with ROWNUM

If you only want the first row, use ROWNUM:

DECLARE

   v_name VARCHAR2(50);

BEGIN

   SELECT name INTO v_name

   FROM employees

   WHERE department_id = 10

   AND ROWNUM = 1;

   DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);

END;

/

---

🔹 Option 2: Use AGGREGATE FUNCTIONS

If you want one value, you can use MIN, MAX, COUNT etc.:

DECLARE

   v_name VARCHAR2(50);

BEGIN

   SELECT MIN(name) INTO v_name

   FROM employees

   WHERE department_id = 10;

   DBMS_OUTPUT.PUT_LINE('First employee alphabetically: ' || v_name);

END;

/

---

🔹 Option 3: Use CURSORS to Handle Multiple Rows

Cursors let you loop through multiple rows:

DECLARE

   CURSOR emp_cur IS

      SELECT name FROM employees WHERE department_id = 10;

   v_name employees.name%TYPE;

BEGIN

   FOR rec IN emp_cur LOOP

      DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.name);

   END LOOP;

END;

/

---

🔹 Option 4: Use BULK COLLECT for Collections

If you want to fetch multiple rows into a PL/SQL table:

DECLARE

   TYPE t_names IS TABLE OF employees.name%TYPE;

   v_names t_names;

BEGIN

   SELECT name BULK COLLECT INTO v_names

   FROM employees

   WHERE department_id = 10;

   FOR i IN 1 .. v_names.COUNT LOOP

      DBMS_OUTPUT.PUT_LINE('Employee: ' || v_names(i));

   END LOOP;

END;

/

---

Step 4: Best Practices

Avoid using SELECT INTO if the query may return multiple rows.

Use EXCEPTION blocks if you want to catch and handle ORA-01422.

Always design your WHERE clause carefully to ensure it returns the expected result.

For multiple-row queries, prefer cursors or bulk operations.

---

Conclusion

The ORA-01422 error tells you that your query returned more than one row where only one was expected.

Depending on your requirement, you can:

✔️ Restrict the query to return one row.

✔️ Use aggregate functions.

✔️ Fetch multiple rows with cursors or collections.

👉 This error is often about query design—so always make sure your query logic matches your expected output.