How can use oracle pipelined function on query to fetch data only first time.
example:
create or replace function best_employees return my_type pipelined;
select * from employees a
join table(best_employees) b
on a.employee_id = b.employee_id;
this query is calling best_employees function more than one time. It must call only first time. How can i do this. Thanks.
While you could store the results in a package collection of type my_type and write the function to return that if it contained values instead of re-executing the query, the simpler and more reliable approach would be to use the result_cache hint in its query.
select /*+ result_cache */ kitten_id, cuteness from kittens where colour = 'BLACK';
(There is also a result_cache option for functions but it cannot be used for pipelined functions.)
