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

5 comments:

  1. I am getting following error while entering Order for a KIT item
    ORA-01422: exact fetch returns more than requested number of rows in Package OE_Order_PVT Procedure Lines

    ReplyDelete
    Replies
    1. just check query at the line of error is returning a single row or not. If not you have to change it to cursor loop or change your query to return 1 record

      Delete
  2. I solve my problem with below query
    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;
    /

    my problem is retruning multiple rows to this query
    after change the above query i got the reult

    create or replace function get_leader_dpl_name(t_type_p in varchar2) return varchar2
    as

    V_dpl_name varchar2(50);
    begin
    for c in (select dpl_name into V_dpl_name from bi_mg_person where ebs_id=t_type_p)
    loop
    V_dpl_name := c.dpl_name;
    end loop;
    return V_dpl_name;
    end;

    ReplyDelete
  3. CREATE OR REPLACE FUNCTION FUNCTION2 (v_sy IN VARCHAR2)
    return varchar2
    as

    V_DL01 varchar2(50);
    BEGIN
    for d in (SELECT DRDL01 into V_DL01
    FROM PRODCTL.F0005
    where DRSY = 'v_sy')
    LOOP
    V_DL01:= d.DRDL01;
    END LOOP;
    RETURN V_DL01;
    end;
    ----------------
    i tried using same code as explained above but i am getting null values as output

    ReplyDelete