My solution was to create a pipelined function. The advantages are that the query can be a single line:
select * from table(yourfunction(param1, param2));To define the function you would need to do something like the following:
-- Declare the record columns
TYPE your_record IS RECORD(
my_col1 VARCHAR2(50),
my_col2 varchar2(4000)
);
TYPE your_results IS TABLE OF your_record;
-- Declare the function
function yourfunction(a_Param1 varchar2, a_Param2 varchar2)
return your_results pipelined is
rt your_results;
begin
-- Your query to load the table type
select s.col1,s.col2
bulk collect into rt
from your_table s
where lower(s.col1) like lower('%'||a_Param1||'%');
-- Stuff the results into the pipeline..
if rt.count > 0 then
for i in rt.FIRST .. rt.LAST loop
pipe row (rt(i));
end loop;
end if;
-- Add more results as you please....
return;
end find;
And as mentioned above, all you would do to view your results is:
select * from table(yourfunction(param1, param2)) t order by t.my_col1;