The problem with Hot-Patching and having Result-Cached Functions

Sometimes we bypass some of the documentation: sometimes that is due to time constraints, other times it's due to "I already know this" or cross-reading.
Or maybe we read but forgot :-).

I wanted to store here (so I will remember) the solution for a problem I got when updating a package that had result-cached functions and those functions where used by other result-cached functions in another package.



I will leave the official documentation to explain the situation and the solution.

Hot-Patching PL/SQL Units on Which Result-Cached Functions Depend

When you hot-patch a PL/SQL unit on which a result-cached function depends (directly or indirectly), the cached results associated with the result-cached function might not be automatically flushed in all cases.

For example, suppose that the result-cached function P1.foo() depends on the packaged subprogram P2.bar(). If a new version of the body of package P2 is loaded, the cached results associated with P1.foo() are not automatically flushed.

Therefore, this is the recommended procedure for hot-patching a PL/SQL unit:

Note:
To follow these steps, you must have the EXECUTE privilege on the package DBMS_RESULT_CACHE.

1.

Put the result cache in bypass mode and flush existing results:

BEGIN
DBMS_RESULT_CACHE.Bypass(TRUE);
DBMS_RESULT_CACHE.Flush;
END;
/

In an Oracle RAC environment, perform this step for each database instance.
2.

Patch the PL/SQL code.
3.

Resume using the result cache:

BEGIN
DBMS_RESULT_CACHE.Bypass(FALSE);
END;
/

In an Oracle RAC environment, perform this step for each database instance.

Source

I think that as best-practice anytime we have to upgrade a PL/SQL unit with result-cache we should do this (one time for the all upgrade (if there is more)). The downside is that:
- we will clean the result-cache cache ( :-) ) and that some of that cache maybe didn't need that
- the current sessions calling the function will be slower (not using the cache).

But the profit is huge : avoids some hard to find errors :-)

PS: another way would be to use editions (only available from 11R2 up)

Happy coding!