These are very carefully written accounts that show what can go wrong if you break the rules for the safe use of the PL/SQL Function Result Cache. Jonathan's analysis, and a very fruitful private follow-on discussion showed that we'd neglected to state these safety rules clearly in our documentation.
To bridge the gap until the version of the Oracle Database documentation that fixes the account is available, and to provide a rather different perspective on the whole business than Jonathan's post did, we offer the following essay:
How to write a safe result-cached PL/SQL function
A result-cached PL/SQL function is safe if it always produces the same output for any input that it would produce were it not marked with the result_cache keyword. This safety is guaranteed when, and only when, these conditions are met:
* all tables that the function accesses are ordinary, non-Sys-owned permanent tables in the same database as the function
* the function’s result must be determined only by the vector of input actuals together with the committed content, at the current SCN, of the tables that it references
* when the function is run, it must have no side-effects.