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 to Vikram Singh Rathore for a suggestion for this page.