if we need to catch an exception we have following 2 ways
1) user defined exception like
declare
e_more_rows EXCEPTION;
PRAGMA EXCEPTION_INIT(e_more_rows, -01422);
a scott.emp.empno%type;
begin
select empno into a from scott.emp where rownum < = 2;
exception
when e_more_rows then
-- do something here
null;
end;
/
2) use exception number in exception block
declare
a scott.emp.empno%type;
begin
select empno into a from scott.emp where rownum < = 2;
exception
when others then
if sqlcode = -01422 then
-- do something here
null;
end if;
end;
/
I think it would be handy we exception block can directly catch the exception by its number like
declare
a scott.emp.empno%type;
begin
select empno into a from scott.emp where rownum < = 2;
exception
when -01422 then
null;
end;
/