Table Functions - Recap

Suppose we have two schema-level types, a tuple analogous to a table row and a table of these, defined thus…

create type lookup_row as object ( idx number, text varchar2(20) );
/
create type lookups_tab as table of lookup_row;

/

We can then write a PL/SQL function which returns an instance of the table thus…

create or replace function Lookups_Fn return lookups_tab is
  v_table lookups_tab;
begin

  /*
  To extend a nested table, you must use the built-in procedure EXTEND,
  but to extend an index-by table, you just specify larger subscripts.
  */
  v_table := lookups_tab ( lookup_row ( 1, 'ONE' ) );
  for j in 2..9
  loop

    v_table.Extend;
    if j = 2 then v_table(j) := lookup_row ( 2, 'two' );
    elsif j = 3 then v_table(j) := lookup_row ( 3, 'THREE' );
    elsif j = 4 then v_table(j) := lookup_row ( 4, 'four' );
    elsif j = 5 then v_table(j) := lookup_row ( 5, 'FIVE' );
    elsif j = 6 then v_table(j) := lookup_row ( 6, 'six' );
    elsif j = 7 then v_table(j) := lookup_row ( 7, 'SEVEN' );
    else             v_table(j) := lookup_row ( j, 'other' );

    end if;
  end loop;
  return v_table;
end Lookups_Fn;
/

We can then invoke it in the FROM list of a SELECT statement thus…

select * from table ( cast ( Lookups_Fn() as lookups_tab ) );

This allows a table to be synthesized by computation. For example, the function might call Utl_File procedures (to parse data that cannot be handled by the SQL*Loader utility or by the external table feature), or might call C routines (via the callout framework) which access arbitrary external data sources. Or it might access database tables and perform transformations which cannot be expressed with pure SQL and SQL functions. The SELECT statement can be used to define a view, and/or combined with other tables in the FROM list in an arbitrarily complex SQL statement.

A table function, then, is a PL/SQL function which can be invoked in the FROM clause of a SQL SELECT clause. A table function which exploits new Oracle9i functionality, which we expect all table functions to do, can only be invoked in the FROM clause of a SQL SELECT clause.