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

7 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
  4. yeah, um ... no.

    First, examine how the system works, to determine if the returned values can be different (that depends on how normalized/denormalized the database is).

    If all records will indeed return the same value,you can do a "select distinct", or a "add a rownum = 1" clause.

    If the values are not unique, then as a DB programmer you DON'T choose which one applies; you talk the the Business Analysts to find out what the correct logic should be.

    ReplyDelete
  5. check sub query and use rownum<2

    ReplyDelete