The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.

Turns out that while all of the above is unambiguously correct for packages, when it comes to functions, the PL/SQL compiler is downright funky - and it messed up our quiz! Check this out (many thanks to Iudith for the code example and detailed analysis):

If when you define your function you include the RESULT_CACHE keyword after the AUTHID CURRENT_USER clause, you will see the above error. If, however, you reverse the order of those clauses, the function compiles - but the results are not cached.

Now that right there is funky stuff.

We will take the following steps for this quiz:

1. Everyone gets credit for the two choices listed above (bad luck that this affected two of the choices).Your answers will be changed to reflect this.

2. We will change the text of the question so that it explicitly asks about using both invoker rights and result cache in a package. That way this ambiguity will be avoided.

3. We'll notify the PL/SQL product manager about this glitchy behavior, just in case they are not aware.

This PLS-00999 compilation error + the remark itself that "it may be temporary" looks like telling us that Oracle did not yet decide definitely what to do with this issue, to allow it ... not to allow it ...

Otherwise, probably a clear compilation errorwould have been issued and not the "00999" ...

In any way, the order of the clauses should not have mattered, the result should have been identical and this is a bug.

The whole issue of RESULT_CACHE is maybe "new enough" so that we can understand and forgive Oracle that it is still undergoing development changes, like for example the issue of lifting the RELIES_ON clause in 11gR2 and making it happen automatically ... at least for static SQL.

If we as a community can help Oracle to improve itself, that is already a remarkable issue,I strongly hope that they will find our groupreliable enough for being listened to :) :) :)

I think we all really deserve it and Steven is our best advocate in this direction.

For yet more funky compiler behaviour ... on our 11.2 systems with a standalone function if you explicitly specify AUTHID DEFINER after RESULT_CACHE in the function header it will compile but is completely ignored just like your example with AUTHID CURRENT_USER. Specifying AUTHID DEFINER before RESULT_CACHE in the function header works as expected. Buggy!