Pipelined Table Functions - New in Oracle9i

The above functionality is available pre-Oracle9i. However, it has the limitation that the function must run to completion, storing all the rows it computes in the PL/SQL table before even the first row can be delivered. (There are other limitations, see below.) Oracle9i introduces the pipelined construct which allows the procedure to be rewritten thus…

create or replace function Lookups_Fn return lookups_tab
  pipelined
is
  v_row lookup_row;
begin

  for j in 1..10
  loop
    v_row :=
      case j
        when 1 then lookup_row ( 1, 'one' )
        --...
        when 7 then lookup_row ( 7, 'seven' )

        else        lookup_row ( j, 'other' )
      end;
    pipe row ( v_row );
  end loop;
  return;
end Lookups_Fn;
/

Thus each row is delivered as soon as it is ready, so that the response time characteristics of a table function are symmetrical with those of a rowsource based on a table scan or an index scan. (For performance, the PL/SQL runtime system delivers the rows from a pipelined table function in batches.)

Note: the procedure body now mentions only rows (i.e. not the table), and the table is just implied by the return type. (For elegance, the IF construct has been replaced with the new CASE formulation.) The same syntax as above can be used to select from the table function, but it can now be simplified thus…

select * from table ( Lookups_Fn );

(The invocation will be written Lookups_Fn() in the following to emphasize its status as a function.)

Oracle9i also introduces the possibility to create a table function which returns a PL/SQL type thus…

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

In the limit, a PL/SQL type may be defined in the declare section of an anonymous block and hence have no persistence. However, to be useful in connection with table functions, the PL/SQL types must be declared in a package, and so when discussing table functions they are usually referred to as package-level types (in contrast to schema-level types).

Note: A table function which returns a package-level type must be pipelined. Moreover, the simpler SELECT syntax (without the CAST) must be used.