ORACLE TOO_MANY_ROWS EXCEPTION HANDLING

ORACLE TOO_MANY_ROWS EXCEPTION HANDLING

When Select Into statement in PL/SQL block returns more than 1 row. and select into variable holds single value, at that time Too_Many_rows exception will occur.

declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 7

for ex:TOO_MANY_ROWS exception handling

declare
cursor c1 is select * from emp;
e emp%rowtype;
begin
open c1;
loop
   select * into e from emp; --this throws too_many_rows exception
   exit when c1%notfound;
       dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno);
end loop;
exception
when TOO_MANY_ROWS then
         dbms_output.put_line('select into returning more than one row');
end;
/

OUTPUT:

select into returning more than one row

PL/SQL procedure successfully completed.
 

Solution:

    -- in this case replace select into with fetch cursor statement

declare
cursor c1 is select * from emp;
e emp%rowtype;
begin
open c1;
loop
   --select * into e from emp;
   fetch c1 into e;
   exit when c1%notfound;
       dbms_output.put_line(e.empno|| ' '|| e.sal||' '||e.deptno);
end loop;
exception
when TOO_MANY_ROWS then
         dbms_output.put_line('select into returning more than one row');
end;

Tags:ORACLE TOO_MANY_ROWS EXCEPTION HANDLING, Handling too many rows exception in oracle, oracle exception block, oracle cursors.%rowtype.