sqloracle-databasepipelined-function

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;

Solution

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