SQL & PL/SQL

Hello,

please, could anybody explain why the NO_DATA_FOUND exception is not propagated into the SQL statement result? Let's have the following package:

-- NO_DATA_FOUND_TEST specification
CREATE OR REPLACE package no_data_found_test as
  function find_dname (
    p_deptno dept.deptno%type
  ) return dept.dname%type;
  function find_too_many_rows return dept.dname%type;
end no_data_found_test;
/
-- NO_DATA_FOUND_TEST body
CREATE OR REPLACE package body no_data_found_test as
  function find_dname (
    p_deptno dept.deptno%type
  ) return dept.dname%type
    as
    l_dname dept.dname%type;
  begin
    select dname into l_dname from dept where deptno = p_deptno;
    return l_dname;
  end find_dname;
  
  function find_too_many_rows return dept.dname%type as
    l_dname dept.dname%type;
  begin
    select dname into l_dname from dept;
    return l_dname;
  end find_too_many_rows;
  
end no_data_found_test;
/

Now when I execute the statement

select no_data_found_test.find_dname(10) from dual;

and PL/SQL block

begin
  dbms_output.put_line('Dname: ' || no_data_found_test.find_dname(10));
end;

I'm getting output 'ACCOUNTING' (exactly as expected for existing department).

Now let's do the same for non-existing department

select no_data_found_test.find_dname(1000) from dual;

NO_DATA_FOUND_TEST.FIND_DNAME(1000)

null

Quite surprisingly I'm getting null as the result even if the NO_DATA_FOUND exception is raised inside of the package. If I run the same in PL/SQL anonymous block

begin
  dbms_output.put_line('Dname: ' || no_data_found_test.find_dname(1000));
end;

result is ORA-01403: no data found (exactly as expected).

If we do the same with TOO_MANY_ROWS in SQL statement

select no_data_found_test.find_too_many_rows from dual;

the result is

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

which is expected (the same result also when invoked from within PL/SQL block).

Please, could anybody explain what is the difference between NO_DATA_FOUND and TOO_MANY_ROWS exception handling and why the former is being "obfuscated" and instead of exception raising it silently returns null, while the latter honestly crashes and propagates the TOO_MANY_ROWS exception into the select statement?

Thanks a lot

Pavel