Hi- My general understanding of function using result_cache is that if the underlying data has changed, the function knows to self-invalidate the cached result. What if the function doesn't use any underlying data, but is doing math off sysdate? How do we control the cached result invalidation?

A similar function to the example provided is from some big stupid looping legacy query to get the Sunday of any number of weeks out. Rather than rewrite the code to eliminate need for a function, I got lazy and threw a RESULT_CACHE on the function to speed everything up. All last week it correctly returned the Sunday of last week for select find_sunday(1) from dual. This Monday, it was still returning last Sunday! The result was still cached, and of course it should be; there is no table referenced in the function that would invalidate the cached result. I assume RELIES_ON could help me here, but all examples of RELIES_ON use a table name. I found I could get RELIES_ON(sysdate) to compile... but I presume that would invalidate immediately because it's never the same sysdate for long. On the flip side, that might never invalidate because sysdate isn't really "data" and it's definition is not changing ever. RELIES_ON(trunc(sysdate)) will not compile, but basically that's what I would be looking for in a function like this...

In the end I just got rid of the function (and the cursor, and the when others... you pick your battles), but I'm still left with the question of how to make logic-only, probably date based, function where the invalidation could be controlled?

Thanks, John


CREATE OR REPLACE FUNCTION FIND_SUNDAY (p_week_num IN NUMBER)
RETURN DATE RESULT_CACHE
IS
tmp_dt DATE;
tmp_num NUMBER;
BEGIN
tmp_dt := TRUNC (SYSDATE);
tmp_num := (p_week_num - 1) * 7;


IF p_week_num < 1
THEN
tmp_dt := TO_DATE ('01/01/2000', 'MM/DD/YYYY');
ELSE
SELECT (TRUNC (SYSDATE, 'd') + tmp_num) INTO tmp_dt FROM DUAL;
END IF;


RETURN (tmp_dt);
END FIND_SUNDAY;