hakuna matata hakuna matata - 1 month ago 8
SQL Question

No data found When Piping Row

I have a function that returns a list of records, and then im looping over the list and piping them, however during piping I am getting

ORA-01403: no data found
error.

Below is the code I am using, and I am getting this error on some rows, not all of them.

NOTE:
tab_pipe.t_tab
and
tab.t_tab
are tables of the same record
tab.r_tab
.

Function pipelinedFunction(ref varchar2, seq varchar2) Return tab_pipe.t_tab pipelined Is
pragma autonomous_transaction;
errtxt varchar2(400);
tab tab.t_tab;
begin
tab := generate_table(ref, seq);

for i in 1 .. tab.count loop
begin
pipe row(tab(i));
EXCEPTION
when others then
v_errtxt := sqlerrm;
insert into test_kc values('an error occurred piping the row i = ' || i || ' - sqlerrm = ' || v_errtxt); commit;
end;
end loop;

return;
end pipelinedFunction;

Answer

Maybe there is no entry in tab for every value of i.

Try a loop using first and next

declare
  l_index PLS_INTEGER;
BEGIN
  l_index := tab.FIRST;

  WHILE (l_index IS NOT NULL)
  LOOP
    pipe row(tab(l_index)); 
    l_index := tab.NEXT(l_index);
  END LOOP;
END;