No data found When Piping Row

Question

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;

score 2 · Accepted Answer · answered Oct 26 '16 at 12:28

2

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;

answered Oct 26 '16 at 12:28

Rene

10,391
5
33
46

I no logner get the issue and all the records are returning, but what do you mean by `Maybe there is no entry in tab for every value of i.`, do you mean a records at a certain index has all of it's column values null? – hakuna matata Oct 26 '16 at 12:44
You loop from 1 to tab.count. Maybe tab(3) does not exist? Can't say anything about that without knowing what generate_table does. – Rene Oct 26 '16 at 12:59
But before I used `1 .. tab.count` I was using `tab.first .. tab.last` and was still getting the same issue. Could there be something from the function that generates the table of records that is causing this issue too? – hakuna matata Oct 26 '16 at 13:01
It means tab has no records. Another solution is `IF tab is not null THEN IF tab.count > 0 THEN for i in 1 .. tab.count loop ... END LOOP; END IF; END IF;` – Wernfried Domscheit Oct 26 '16 at 14:29

No data found When Piping Row

1 Answers1