Exceptions in PL/SQL

set feedback off
set serveroutput on

create table foo (
  a  varchar2(10),
  b  varchar2(10),
  i  number
);

insert into foo values ('xxx','yyy',1);
insert into foo values ('zzz','aaa',1);
insert into foo values ('qqq','mmm',3);

commit;

declare
  l_a foo.a%type;
  l_b foo.b%type;
begin
  select a,b into l_a, l_b from foo where i=1;
  dbms_output.put_line('a: ' || l_a || ', b: ' || l_b);
exception 
  when too_many_rows then
    dbms_output.put_line('*** Exc: too many rows');
  when no_data_found then
    dbms_output.put_line('*** Exc: no data');
end;
/

declare
  l_a foo.a%type;
  l_b foo.b%type;
begin
  select a,b into l_a, l_b from foo where i=2;
  dbms_output.put_line('a: ' || l_a || ', b: ' || l_b);
exception 
  when too_many_rows then
    dbms_output.put_line('*** Exc: too many rows');
  when no_data_found then
    dbms_output.put_line('*** Exc: no data');
end;
/

declare
  l_a foo.a%type;
  l_b foo.b%type;
begin
  select a,b into l_a, l_b from foo where i=3;
  dbms_output.put_line('a: ' || l_a || ', b: ' || l_b);
exception 
  when too_many_rows then
    dbms_output.put_line('*** Exc: too many rows');
  when no_data_found then
    dbms_output.put_line('*** Exc: no data');
end;
/

drop table foo;

create or replace package exc as

  some_exc exception;

  pragma exception_init(some_exc, -20005);

  procedure raise_exc;
end exc;
/

create or replace package body exc as

  procedure raise_exc is begin 
    raise some_exc;
  end raise_exc;

end exc;
/

create or replace package use_exc as
  procedure do;
end use_exc;
/

create or replace package body use_exc as

  procedure do is begin

    exc.raise_exc;

    exception 
      when exc.some_exc then dbms_output.put_line('some_exc');
      when others       then dbms_output.put_line('others'  );
    
  end do;

end use_exc;
/

set serveroutput on
begin
  use_exc.do;
end;
/

some_exc