and the only choice we scored as correct was:
Specifically, the following choice was scored as incorrect:
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 modulesAnd 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 modulesIf 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.