Простейший пример использования табличный функций (pipe row)

Создаем тип для нужной нам табличной функции:

create or replace package My_Types is
  type lookup_row is record ( idx number, text varchar2(20) );
  type lookups_tab is table of lookup_row;
end My_Types;

Теперь очередь самой функции:

create or replace function Lookups_Fn return My_Types.lookups_tab
  pipelined
is
  v_row My_Types.lookup_row;
begin
  for j in 1..10
  loop
    case j
      when 1 then v_row.idx := 1; v_row.text := 'one';
      when 7 then v_row.idx := 7; v_row.text := 'seven';
      else        v_row.idx := j; v_row.text := 'other';
    end case;

    pipe row ( v_row );
  end loop;
  return;
end Lookups_Fn;

И, получаем результат:

Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0 
Connected as scott
 
 
SQL> select * from table (lookups_fn);
 
       IDX TEXT
---------- --------------------
         1 one
         2 other
         3 other
         4 other
         5 other
         6 other
         7 seven
         8 other
         9 other
        10 other
 
10 rows selected

Источник.