42.11. Table function

A table function is a function that can be invoked inside the FROM clause of a SELECT statement. They return collections (usually nested tables or varrays, current only support nested tables), which can then be transformed with the TABLE clause into a dataset of rows and columns that can be processed in a SQL statement.

Another explanation of table functions are functions that produce a collection of rows (either a nested table or a varray, only support nested tables current) that can be queried like a physical database table. You use a table function like the name of a database table, in the FROM clause of a query. Example as follows:

create table t1(a int, b int);
insert into t1 (values (1,1), (2,2), (3,3));

create or replace package pkg
as
	type nt is table of t1%rowtype;
	res nt;
	function myfunc() return nt;
end;
/

create or replace package body pkg
as
	function myfunc() return nt
	is
	begin
		res(1) := ROW(1,1);
		res(2) := ROW(2,2);
		res(3) := ROW(3,3);
		return res;
	end;
end;
/

select * from table(pkg.myfunc());

Notice, pipelined table function is not support.