Returning a 'table' from a PL/SQL function
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 return_table;
/
Here's how the function is used:
select * from table(return_table);
1 one
2 two
3 three
Returning a dynamic set
Now, the function is extended so as to return a dynamic set.
The function will return the object_name and the object_id from user_objects whose object_id is in the range that is passed to the function.
create or replace function return_objects(
p_min_id in number,
p_max_id in number
)
return t_nested_table as
v_ret t_nested_table;
begin
select
cast(
multiset(
select
object_id, object_name
from
user_objects
where
object_id between p_min_id and p_max_id)
as t_nested_table)
into
v_ret
from
dual;
return v_ret;
end return_objects;
/
And here's how the function is called.
select * from table(return_objects(37900,38000));
