Table function can perform row to column operations on array data, and parameter types can be arrays or nested tables, or function return values (arrays/nested tables). It can also be used in query statements. To adapt to the table function of Oracle Database, if the table function is used in the from clause and the data returned by the table function is a single column, the column name is changed to column_value. This feature takes effect in Oracle compatibility mode.

Array Example:

select table(array[1,2,3]) as a;

Nested Table Example:

create type kk is table of int;
select table(kk(1,2,3)) as a;

Example of function return value：

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());

Example of using query statements:

create type kk is table of int;
create table t100 (a int, b kk);
insert into t100 values(1, kk(1, 2));
insert into t100 values(2, kk(3, 4, 5));
select a, b_table from t100, table(b) b_table;