18 May 2011

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

9 comments:

  1. yes, saved by a technicality! Many thanks Ludith!

    ReplyDelete
  2. This is one more exciting thing about PL/SQL compiler.

    The proposal looks absolutely valid, even though my and some other players scores will be diminished against rescore.

    And also it's interesting how this function behaves having AUTHID CURRENT_USER and RESULT_CACHE specified together.

    Cheers,
    Sergey

    ReplyDelete
  3. Hello All,
    That is amazing !!!!

    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 error
    would 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 group
    reliable enough for being listened to :) :) :)

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

    Thanks & Best Regards,
    Iudith

    ReplyDelete
  4. Just from curiosity - in which Oracle version was it tested? Both 11.1 and 11.2?

    ReplyDelete
  5. Three comments:

    1. PLS-0999 happens in all cases in 11.2.0.2, so in previous versions it's a bug.

    2. The answer about "CURRENT_USER setting will be ignored" - is true, actually RESULT_CACHE will be ignored.
    I disagree with rescore for this choice.

    3. So I think that it's possible to consider "No choices are correct" as a correct choice.

    ReplyDelete
  6. I make corrections on an individual choice basis, so if you chose "No choices are correct", your score was adjusted sa well.

    Disagreement with rescore on "will be ignored" choice: without the compiler "glitch", the compilation error is raised, so it is not a matter of IGNORING the result cache. It is REJECTED.

    ReplyDelete
  7. About [Answer ID: 1088] - regardless of the compiler "glitch", this choice is incorrect. And so there is need no rescore for it.

    ReplyDelete
  8. The docu says in http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/subprograms.htm#g3335204

    Restrictions on Result-Cached Functions

    To be result-cached, a function must meet all of these criteria:

    It is not defined in a module that has invoker's rights or in an anonymous block.
    ...

    Maybe you should just refer in the question to the docu. The answer is clear there at least, even though not necessarily compatible with actual PL/SQL functionality.

    ReplyDelete
  9. 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!

    ReplyDelete