Oracle: return a »table« from a function
With collections and the table() function, a function can return a table that can be queried in an SQL statement. This is demonstrated in the following example.
Record type
First, we need to create a record type. In this example, the records consist of two attributes: i, a number and n, a varchar2.
The record type is created with create type:
create or replace type t_record as object (
i number,
n varchar2(30)
);
/
Table type
Based on the record type, we can now create a table type.
create or replace type t_table as table of t_record;
/
Function
With the table type, we're ready to create a function.
Note: the function returns a t_table.
create or replace function return_table return t_table as
v_ret t_table;
begin
--
-- Call constructor to create the returned
-- variable:
--
v_ret := t_table();
--
-- Add one record after another to the returned table.
-- Note: the »table« must be extended before adding
-- another record:
--
v_ret.extend; v_ret(v_ret.count) := t_record(1, 'one' );
v_ret.extend; v_ret(v_ret.count) := t_record(2, 'two' );
v_ret.extend; v_ret(v_ret.count) := t_record(3, 'three');
--
-- Return the record:
--
return v_ret;
end return_table;
/
Using the function
In order to use the function's returned value as a table in a SQL statement, we have to enclose the function within the table() statement. From the SQL's perspective, the table(…) construct behaves as though it were an actual table.
select * from table(return_table);
Using bulk collect
The previous function is rather boring in that it returns the same result set each time it is called.
The following function makes that a bit more dynamic. It takes a parameter that specifies the maximum count of records to be returned.
Then, it uses rownum and bulk collect to select a result set into a variable of type t_table and returns it.
create or replace function return_objects (
p_max_num_rows in number
)
return t_table as
v_ret t_table;
begin
select
t_record(rownum, object_name)
bulk collect into
v_ret
from
user_objects
where
rownum <= p_max_num_rows;
return v_ret;
end return_objects;
/
Using the function
Again, this function is used with the table(…) construct:
select * from table(return_objects(5));
Cleaning up
Dropping the functions and record types to clean up:
drop type t_table;
drop type t_record;
drop function return_table;
drop function return_objects;
Thanks
Thanks to Vikram Singh Rathore for a suggestion for this page.
Original Page Source: renenyffenegger.ch/notes/development/databases/Oracle/PL-SQL/collection-types/return-table-from-function/index
