12 August 2011

Wrong Scoring for Stateful Package Quiz? (5193)

The 11 August quiz explored the concept of "state" in a package and asked you to identify ways to avoid the raising of the ORA-04068 error.

Two players wrote with objections to how the answers were scored. Here are their comments:

I guess answer 7626 was supposed to be correct, but the offered solution will not work. The new package body should reference the variable declared in plch_pkg_constant: CREATE OR REPLACE PACKAGE BODY plch_pkg IS FUNCTION language_vendor RETURN VARCHAR2 IS BEGIN RETURN plch_pkg_constant.g_language; -- <== END language_vendor; END plch_pkg; So "Move the declaration of the variable to another package" is correct, but "as shown below" is not. 

My reading of the choices is that none of them will give you the response of the "newly changed variable", however my feeling that the "Move the variable to the package" is the correct choice because, I am thinking, that that will allow you to repeatedly call the function even though the response will always be "Oracle Corporation" and not what you set the variable to. 

My response: I can see why these players were caught up on this issue, but I believe it results from a mis-reading of the quiz question, which ends with:

"Which of the choices describe a way to change the code in plch_pkg so that a user connected to the SCOTT schema can call the hr.plch_pkg.language_vendor function repeatedly, even if the default value of the g_language variable is changed in the HR schema?"

Notice that I say "call the hr.plch_pkg.language_vendor function repeatedly". The g_language variable (which gives "state" to the package, leading to the ORA-04068 error) is not used by the language_vendor function. Changing the value of this variable does not affect this function in any way. So I don't think this statement is true: "The new package body should reference the variable declared in plch_pkg_constant."

Of course, in the "real world", it is unlikely that you can move a variable from one package to another without having a ripple effect through other subprograms and program units. But I don't think that is an issue here.

Your thoughts?


  1. Hello All,
    The essential point here is exactly the fact that the function IS NOT using the variable that made the package "stateful", so the variable could be safely moved to another package, in the context of this quiz.
    Otherwise, if the function were using that variable as a return value, then moving it to another package would have only caused the session to depend also on a different stateful package, in addition to depending on the modified package, now become stateless.
    So, in that case, the choice would have been indeed incorrect.
    But, as it was presented in the quiz, it was
    correct, so I think it was scored correctly.

    Thanks & Best Regards,

  2. Hello Steven,

    I think you're missing something here. If the point were just to make it
    possible to "call the hr.plch_pkg.language_vendor function repeatedly,
    even if the default value of the g_language variable is changed in the
    HR schema" then what you had to do would be exactly NOTHING. You proved that
    yourself: "Tiger executes the same block again". You can call still the
    function, however the function does not do what it is expected to do:
    return the new value. Instead, it raises an exception.

    The point of question can only be: will the solution offered make the
    function do what it is expected to do. And it does not. It still returns
    the old value, not the one you have changed, not even if someone uses
    a new connection to call it.

    Imho receiving an error message would be much better than receiving
    wrong values without notice.

    Regards, Urs

  3. The function returns the "old value" because the value that the function returns was not dependent on the constant. So I must agree when you say "the function does not do what it is expected to do: return the new value." The point of this quiz is to focus on the ORA-04068 error and what causes it.

    Yes, sure, I suppose you could argue that our question should have said "Tiger executes the same block again without an error" but I think that was well understood.

  4. Hello All,
    I looks like maybe "the point" of moving the variable to another package was not strongly enough emphasized as a "tool" for reducing unnecessary dependencies, in other words, there is no reason to keep a variable in a place where changing its value can cause unnecessary package state discard errors, while the variable itself is not effectively needed/used in that place, as in the case of our function.

    This might be a reason that most objections refer to the function return value, which was not the subject of the quiz.

    Thanks & Best Regards,