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));

Using this technique

poor man's text index is an example that uses this technique to search in the middle of words in a table. (ie where filed like '%word%')

Thanks

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


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback