Oracle Pl Sql : Devolver un objeto tabla desde una función

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