Search notes:

Oracle: Return a «table» from a function

A PL/SQL function that returnes a nested table collection can be programmed so that it returns a «virtual table» that can be queried in an SQL statement like an «ordinary» table.
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)
);
/
Github repository oracle-patterns, path: /PL-SQL/CollectionTypes/return-table-from-function/create-record-type.sql

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;
/
Github repository oracle-patterns, path: /PL-SQL/CollectionTypes/return-table-from-function/create-table-type.sql

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;
/
Github repository oracle-patterns, path: /PL-SQL/CollectionTypes/return-table-from-function/create-function.sql

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);
Github repository oracle-patterns, path: /PL-SQL/CollectionTypes/return-table-from-function/select-from-function.sql

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;
/
Github repository oracle-patterns, path: /PL-SQL/CollectionTypes/return-table-from-function/bulk-collect.sql
See also execute immedidate … bulk collect.

Using the function

Again, this function is used with the table(…) construct:
select * from table(return_objects(5));
Github repository oracle-patterns, path: /PL-SQL/CollectionTypes/return-table-from-function/select-from-function-2.sql

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;
Github repository oracle-patterns, path: /PL-SQL/CollectionTypes/return-table-from-function/clean-up.sql

Thanks

Thanks to Vikram Singh Rathore for a suggestion for this page.

See also

Pipelined functions.
Table valued functions in SQL Server
The row source (plan operator) used to fetch «rows» from a table function is COLLECTION ITERATOR PICKLER FETCH.
Poor man's text index in Oracle is an example that uses this technique to search in the middle of words in a table. (ie where filed like '%word%').

Index