1

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.

Ayubxon Ubaydullayev
  • 339
  • 2
  • 16

1 Answers1

0

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

William Robertson
  • 15,273
  • 4
  • 38
  • 44
  • So is it "not working" or "working", and what do those terms mean? The function will of course be executed every time it is called. The `result_cache` hint is to cache the results from the SQL inside it. – William Robertson Mar 06 '17 at 13:27
  • best_employees function working everytime. I want that it works only first time. Then it must take from cache – Ayubxon Ubaydullayev Apr 03 '17 at 07:35