Functions as datasources and NO_DATA_FOUND

Quick post today, on a 'gotcha' with NO_DATA_FOUND.

In Oracle9i, you can define a function that returns a collection and simple do a
SELECT * from table(function_name);

So lets try it.

create or replace function f_alpha
return sys.DBMS_DEBUG_VC2COLL is
 v_ret sys.DBMS_DEBUG_VC2COLL := sys.DBMS_DEBUG_VC2COLL ();
begin
 v_ret.extend(26);
 for i in 1..26 loop
   v_ret(i) := chr(64+i);
 end loop;
 return v_ret;
end;
.
/

select * from table(f_alpha);

That nicely gives you the letters of the alphabet.
Now lets try an error

create or replace function f_alpha
return sys.DBMS_DEBUG_VC2COLL is
 v_ret sys.DBMS_DEBUG_VC2COLL := sys.DBMS_DEBUG_VC2COLL ();
begin
 v_ret.extend(26);
 for i in 1..26 loop
   v_ret(i) := to_char(1/(26-i),'0.000');
 end loop;
 return v_ret;
end;
.
/

select * from table(f_alpha);

In this case we get no data returned, and a 'divide by zero' error.

Lets try it pipelined

create or replace function f_alpha
return sys.DBMS_DEBUG_VC2COLL pipelined is
begin
 for i in 1..26 loop
   pipe row(to_char(1/(26-i),'0.000'));
 end loop;
 return;
end;
.
/

select * from table(f_alpha);

Here we get some rows returned (depending on arraysize) before the error.

Lets try it with a NO_DATA_FOUND error

create or replace function f_alpha
return sys.DBMS_DEBUG_VC2COLL pipelined is
 v_ret varchar2(30);
begin
 for i in 1..26 loop
   select 'Number '||i into v_ret from dual where i != 20;
   pipe row(v_ret);
 end loop;
 return;
end;
.
/

select * from table(f_alpha);

Interesting. At row 20, the no_data_found was returned.
PL/SQL treats it as an exception, and the function fails, but SQL treats a NO_DATA_FOUND as just that and doesn't report an error.

The same happens with a NO_DATA_FOUND from accessing an uninitialized entry in a PL/SQL table:

create or replace function f_alpha
return sys.DBMS_DEBUG_VC2COLL pipelined is
 type tab_number is table of number index by pls_integer;
 t_num tab_number;
begin
 for i in 1..18 loop
   t_num(i) := i;
 end loop;
 for i in 1..26 loop
   pipe row(t_num(i));
 end loop;
 return;
end;
.
/

select * from table(f_alpha);

If you are going to use a function as a data source, then it is advisable to catch the NO_DATA_FOUND and throw it up as another exception. Of course, prior to 10g, this means you'll lose the line where the error actually occurred.

create or replace function f_alpha
return sys.DBMS_DEBUG_VC2COLL pipelined is
 type tab_number is table of number index by pls_integer;
 t_num tab_number;
begin
 for i in 1..18 loop
   t_num(i) := i;
 end loop;
 for i in 1..26 loop
   pipe row(t_num(i));
 end loop;
 return;
exception
 when no_data_found then raise_application_error(-20001,'No Data Found in f_alpha',true);
end;
.
/
select * from table(f_alpha);