Hello
We have a function that should return a number that is selected from a table. We call these function really often (e.g. select max (PKG_FEM_CALL_STATECONTROLLER.GET_THING_IDENT('A2')) from dual connect by rownum <= 1000000) and want to cache the result but can't use "RESULT_CACHE".
Function:
FUNCTION det_function (
V_IN_ID VARCHAR2 )
RETURN NUMBER DETERMINISTIC
AS
V_IDENT NUMBER := - 1;
BEGIN
SELECT
value
INTO
V_IDENT
FROM
my_table
WHERE
ID = V_IN_ID;
RETURN V_IDENT;
EXCEPTION
WHEN VALUE_ERROR THEN
RETURN - 1;
WHEN NO_DATA_FOUND THEN
RETURN - 1;
END;
Now the question is:
When data of my_table has changed does Oracle use the cache or does oracle rexecute the statement in the function we have tested this in ORACLE XE and on ORACLE 12.1 Enterprise and it seems to work, when we have changed the data of my table and commit this. But we really want to know if that is the good way to do this?
Best regards
Ronny
ps
It is just an example with that from dual query. A know what a scalar subquery is.
This function is jused for a rest controller that is calling this check function everytime when new data is arrived so i can't use a scalar subquery. The question is only if data has been changed and is commited, does the function recognize that and does not use the cache for these.