ORA-01403: no data found

Error not thrown in PL/SQL functions executed from SELECT statement

This error is not thrown if the SQL statement is executed in a PL/SQL function which is called from a select statement

In order to demonstrate this behavior, a test table is created and filled with one record:

create table tq84_test (
   id    number,
   val   varchar2(20)
);

insert into tq84_test values (42, 'forty-two' );
commit;

A function is created that selects the val value of the record in that table whose id matches the passed parameter. After selecting this value, an exception is thrown (raise_application_error):

create or replace function tq84_fnc(id number)
   return varchar2
   authid definer
as
   ret varchar2(20);
begin

   select val into ret
   from   tq84_test t
   where  t.id =  tq84_fnc.id;

   raise_application_error(-20800, 'sql%rowcount = ' || sql%rowcount);
  
   return ret;
end tq84_fnc;
/

When exececuted with an id that does not exist in the table, the function expectedly throws ORA-01403: no data found:

begin
   dbms_output.put_line(tq84_fnc(1));
end;
/

The id 42 exists, so the user defined exception (ORA-20800: sql%rowcount = 1) is thrown, which is also expected:

begin
   dbms_output.put_line(tq84_fnc(42));
end;
/

This exception is also thrown in the following select statement:

select tq84_fnc(42) from dual;

However, the following select statement returns, possibly unexpectedly, null (rather than raising ORA-01403):

select tq84_fnc(1) from dual;

Cleaning up

drop function tq84_fnc; 
drop table    tq84_test;

ORA-00100

ORA-01403 seems to be related to ORA-00100:

SQL> exec dbms_output.put_line(sqlerrm(-1403))
ORA-01403: no data found

SQL> exec dbms_output.put_line(sqlerrm(-100))
ORA-00100: no data found

SQL> exec dbms_output.put_line(sqlerrm(100))
ORA-01403: no data found

See also the predefined exception no_data_found.