Returning a table from a PL/SQL function

Oracle, PLSQL

With collections, it is possible to return a table from a pl/sql function. First, we need to create a new object type that contains the fields that are going to be returned:

create or replace type t_col as object ( i number, n varchar2(30) );

Then, out of this new type, a nested table type must be created.

create or replace type t_nested_table as table of t_col;

Now, we’re ready to actually create the function:

create or replace function
return_table return t_nested_table as v_ret
t_nested_table;
begin
v_ret  := t_nested_table();
v_ret.extend;
v_ret(v_ret.count) := t_col(1, 'one');
v_ret.extend;
v_ret(v_ret.count) := t_col(2, 'two');
v_ret.extend;
v_ret(v_ret.count) := t_col(3, 'three');
return v_ret;
end;

Here’s how the function is used:

select * from table(return_table);

Comments