%TYPE and RESULT_CACHE - Correction needed? (9604)

The 15 December quiz tested your knowledge of 11.2's ability to automatically detect when the contents  of a table on which a result cache function relies has been changed (and then invalidate the cache).

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
1

when I should have seen this:

%TYPE Reference
Running plch_func
1
1

That 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
1

The two players who wrote in said they saw these results:

%TYPE Reference
Running plch_func
1
Running plch_func
1

Very 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!

Thanks, Steven