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

ORA-01422: exact fetch returns more than requested number of rows
Cause: The number specified in exact fetch is less than the rows returned.
Action: Rewrite the query or change number of rows requested

Reference: Oracle Documentation

ORA-01422 exception usually occurs when you attempt "select colname into varname" in pl/sql, but your select query returns more than one row.

Example of ORA-01422:

SQL> declare
  2     v_empno number;
  3  begin
  4     select empno into v_empno from scott.emp where deptno=10;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

ORA-01422 may have occured because of incorrect logic implementation and can be resolved in any of the following ways
- Correct the query to return exact one row
- Implement the cursor if the query may return more than one rows
- Bulk collect may be implemented to avoid ORA-01422


Solution of ORA-01422 using query correction:
(lets say the logic was to find empid having max sal in deptno 10):

declare
v_empno number;
begin
select empno into v_empno from scott.emp e 
where deptno = 10 
and sal = 
(
select max(sal) 
from scott.emp e1 
where e.deptno = e1.deptno
);
end;
/

Solution of ORA-01422 using cursor:

declare
v_empno number;
begin
for c in (select empno into v_empno from scott.emp where deptno=10)
loop
v_empno := c.empno;
end loop;
end;
/

Solution of ORA-01422 using bulk collect:

declare
  type mycollectiontype is table of number index by binary_integer;
  mycollection mycollectiontype;
  v_empno number;
begin
select empno  bulk collect into mycollection from scott.emp  where deptno=10;
FOR indx IN 1 .. mycollection.COUNT 
LOOP
v_empno := mycollection(indx);
dbms_output.put_line(v_empno);
END LOOP;
end;
/

Related Links
- Difference Between Cursor And Ref cursor
- View results of refcursor out parameter
- ORA-01403: no data found