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. It's easy for me to say this, because -- as an Oracle insider -- I know the rules. Jonathan's analysis, and a very fruitful private follow-on discussion that he and I had, showed me that we'd neglected to state these safety rules clearly in our documentation. I've filed a doc bug to complain about this. Our discussion prompted me to create my own tests, and as a result, I filed three product bugs. Jonathan cited the doc bug and the product bugs in his blog posting. Anyone with access to My Oracle Support can read them.
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, I have written this essay:
How to write a safe result-cached PL/SQL function
The essay cites the doc bug and the product bugs that I filed too.
My tweet to announce this post was rather cryptic: What did Leo Tolstoy have to say about the PL/SQL Function Result Cache? Read my new blog post to find out. You'll actually have to read my essay to find out. But for those of you who are too busy to read essays, here are are the rules that I hope that I succeed, in my essay, in convincing you are self-evident.
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.