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

SQL>

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

 

Solution:

1.  Make sure the query to return exact one row

 

2.  Implement the cursor if the query may return more than one rows

 

Example:

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

 

Comments