Invoker Rights and Function Result Cache - Oh, Sloppy Compiler! (2322)

In the 17 May quiz, we asked:

Which of the following statements describe what happens when you combine invoker rights (AUTHID CURRENT_USER) with the function result cache?

and the only choice we scored as correct was:

If you include both AUTHID CURRENT_USER and RESULT_CACHE in the header of your function, Oracle will raise a compilation error.

Specifically, the following choice was scored as incorrect:

You can include both AUTHID CURRENT_USER and RESULT_CACHE in the header of your function, but the CURRENT_USER setting will be ignored.

It didn't seem like this would be a very problematic quiz, since Oracle does in fact have an error defined for just this scenario:

PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms in Invoker-Rights modules 

And we verified this with a package as follows:

CREATE OR REPLACE PACKAGE plch_pkg
   AUTHID CURRENT_USER 
IS
   FUNCTION object_name (object_type_in IN all_objects.object_type%TYPE)
      RETURN all_objects.object_name%TYPE
      RESULT_CACHE;
END;
/

That's pretty clear, right? Ha!

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):

SQL> CREATE OR REPLACE FUNCTION F2 (p_id in number)
  2    RETURN VARCHAR2
  3    RESULT_CACHE
  4    AUTHID CURRENT_USER
  5  AS
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE('*** INSIDE FUNCTION ***');
  8    RETURN 'ABC';
  9  END;
 10  /

Function created.

SQL>
SQL> CREATE OR REPLACE FUNCTION F1 (p_id in number)
  2    RETURN VARCHAR2
  3    AUTHID CURRENT_USER
  4    RESULT_CACHE
  5  AS
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE('*** INSIDE FUNCTION ***');
  8    RETURN 'ABC';
  9  END;
 10  /

Warning: Function created with compilation errors.

SQL>
SQL> sho err
Errors for FUNCTION F1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/10     PLS-00999: implementation restriction (may be temporary)
         RESULT_CACHE is disallowed on subprograms in Invoker-Rights
         modules

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.

I look forward to your comments.

Steven