Two players notified us that even though we scored the following choice as incorrect (which meant, in the context of this quiz, that a change to the table would not invalidate the cache):
CREATE OR REPLACE FUNCTION plch_func (n IN NUMBER) RETURN NUMBER RESULT_CACHE IS l_n plch_tab1.n%TYPE := n; BEGIN DBMS_OUTPUT.put_line ('Running plch_func'); RETURN l_n; END;The output from the verification does not support this conclusion.
So, first: the reason that this version of the function should not result in an invalidation of the cache highlights the distinct between dependencies between database objects and "relies on" for a result cache.
The plch_func function depends on the plch_tab1 in the "traditional" sense: since there is a reference to the table in the function through the %TYPE declaration, when this table changes, the function is marked as invalid and will have to be recompiled (note that the Oracle11g fine-grained dependency feature reduces the scenarios under which this invalidation will occur).
But that is a compile-time dependency, and very different from the "relies on" relationship that Oracle tracks automatically for purposes of ensuring clean data in a result cache. In other words, if the contents (but not the structure) of he plch_tab1 table changes, the compilation status of plch_func is not affected.
This is not the case with the result cache feature, precisely because this cache is all about delivering data (the contents of the table) to users.
So...that was the idea: if the only "mention" of plch_tab1 in plch_func is %TYPE, then that function does not rely on plch_tab1, and changes to that table will not force an invalidation and flush of its cache.
Having said that, when I ran the verification code, I noticed that the output did not match my expectations. The first time I ran it, I saw this:
%TYPE Reference 1 1when I should have seen this:
%TYPE Reference Running plch_func 1 1That seemed very strange. It was as though the compilation of the new version of the function did not flush the cache. I then put an update statement before the CREATE OR REPLACE to force the cache to be invalidated. Then I saw the expected results. Then I didn't believe that could possibly be necessary, so I removed the update statement and now do see the expected results consistently:
%TYPE Reference Running plch_func 1 1The two players who wrote in said they saw these results:
%TYPE Reference Running plch_func 1 Running plch_func 1Very odd! I cannot reproduce that, for sure. I'd love to hear about the experiences of any players who have an 11.2 instance with which to work (11.1 still requires that you provide an explicit RELIES ON clause to specify the tables). I have also added that update statement to the verification code.
Let me know what you discover!