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;
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;