Hi!
Version:
Database Patch Set Update : 11.2.0.4.181016 (27734982)
We added a bit of new PL/SQL code to our product, using RESULT_CACHE on PL/SQL functions and discovered during testing that sometimes we get wrong results (from that code).
The issue looks similar as in this older web articles:
- https://magnusjohanssontuning.wordpress.com/2015/08/26/result-cache-wrong-result/
- https://www.slideshare.net/JeffreyKemp1/function-result-cache
The suspect (yet unconfirmed, as we did not found a reliable way to reproduce the issue) is the part of code in a PL/SQL package. The flow is:
- client calls a procedure in the package (only IN parameters)
- that procedure invokes another procedure in the same package
- that procedure invokes another procedure in the same package, that is not public (not declared in the package specification)
- that procedure invokes another procedure in the same package, that is not public (not declared in the package specification)
This last procedure invokes a "select count(*) into ..." statement, that in its WHERE clause calls:
- a DETERMINISTIC function from that package (public, obviously), and
- a RESULT_CACHE function from that package (public)
The last mentioned function again a "select" statement that references a table and a view.
The before last procedure (select count...) the compares the count to a constant and raises an exception if the value is below.
Sometimes this exception is not raised. When trying the same call (from the GUI) again, it usually works correctly.
The last mentioned function has its parameters defined with the %type attribute, like this (not sure if relevant, I'm grasping at straws here):
function fun1(
p_one IN TAB1.PKId%type,
p_two IN TAB1.PKId%type,
p_three IN TAB1.PKId%type default null,
p_four IN TAB1.PKId%type default null
) return INTEGER RESULT_CACHE
Searching support did not reveal anything useful.