Oracle Table Function

Oracle Table Function is introduced in Oracle9i, which is perfectly compatible with the advantages of view and stored procedure:

Example:

1. Table () function:

set feedback offcreate or replace type type_int_date_varchar2  as object (i integer, d date, v varchar2(99))/create or replace type table_int_date_varchar2 as table of type_int_date_varchar2/create or replace function f_int_date_varchar2 return table_int_date_varchar2 as  a_type_int_date_varchar2 table_int_date_varchar2 := table_int_date_varchar2();begin  a_type_int_date_varchar2.extend;  a_type_int_date_varchar2(a_type_int_date_varchar2.count) :=     type_int_date_varchar2(1,to_date('08.01.1947','dd.mm.yyyy'), 'David Bowie');  a_type_int_date_varchar2.extend;  a_type_int_date_varchar2(a_type_int_date_varchar2.count) :=     type_int_date_varchar2(2,to_date('27.01.1756','dd.mm.yyyy'), 'Mozart');  return a_type_int_date_varchar2;end;/select * from table (cast (f_int_date_varchar2() as table_int_date_varchar2));drop function f_int_date_varchar2;drop type table_int_date_varchar2;drop type type_int_date_varchar2;

2. A Function that returns a table of dates in a Range

create or replace type date_obj as object (dt date)/create or replace type date_table as table of date_obj/create or replace function date_range(from_dt in date, to_dt in date)   return date_table as    a_date_table date_table := date_table();    cur_dt date:=from_dt;  begin    while cur_dt <= to_dt loop      a_date_table.extend;      a_date_table(a_date_table.count) := date_obj(cur_dt);      cur_dt := cur_dt + 1;    end loop;  return a_date_table;end date_range;/

select * from  table (cast ( date_range(    to_date('01.01.2002','dd.mm.yyyy'),    to_date('31.01.2002','dd.mm.yyyy')  ) as date_table));