PL/SQL and edition-based redefinition:
the perfect marriage.

  • October 10, 2015

How to write a safe result-cached PL/SQL function

Steven Feuerstein
Developer Advocate for PL/SQL

Jonathan Lewis has recently posted Result Cache and Result Cache 2 on his blog.

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.

Join the discussion

Comments ( 1 )
  • ROBERTO FERNANDES SOBRINHO Tuesday, April 9, 2019
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.