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.

29 September 2011

VALUES OF not a valuable and correct solution? (8304)

The 28 September quiz asked the following:

I must write a procedure that accepts an associative array as a parameter, each of whose elements contains the primary key of a row in that table, and then uses FORALL to update each row identified by one of those primary keys. The associative array may not be densely filled (in other words, there may be index values between the integers returned by the FIRST and LAST methods that are not defined). Which of the choices describe my options for implementing this procedure?

Several players wrote with concerns and questions, most of them having to do with this choice (scored as correct):

Use the VALUES OF clause with the FORALL statement.
Let's address that first, and then touch on the others. Players wrote as follows:"I set the 'VALUES OF'-answer to wrong because this clause needs and only works with indexes of type PLS_INTEGER. The documentation says: "The index collection must be a nested table, or an associative array indexed by PLS_INTEGER or BINARY_INTEGER, whose elements are also PLS_INTEGER or BINARY_INTEGER." You said nothing about type of the index of the associative array, so I thought there are some cases where this solution does not work "

"The last answer, using the "values of clause" is scored as correct, but you need to write some extra code to populate a second collection. Using the same logic the second answer, You will not be able to use FORALL, would be valid choice too. Isn't it?"

"Hello, I don't agree with the scoring of this quiz for the choice 4 ("Use the VALUES OF clause with the FORALL statement.") - I thought this was a trick question since FORALL ... VALUES OF ... cannot be used without the second collection, which was not mentioned in the answer, whereas FORALL ... INDICES OF ... can be used directly on our collection. It would have been better if the answer for choice 4 had some mentioning of the second collection (although it would have hinted that it was a correct answer). Based on the first choice, I expected the wording for the fourth choice to be something similar ("Before executing the FORALL statement populate a second collection with the index values placed in its elements. Then use the VALUES OF clause with the FORALL statement"). Actually, this is not a score adjustment request, just a comment."

My thoughts: I suppose this is yet another example of the difficulties one may encounter with an "all words" quiz (as oppose to one based on specific code). The objective of the quiz was to make sure developers are aware of INDICES OF and VALUES OF as FORALL clauses that allow you to reference a sparse bind array in the FORALL's DML statement.

The question does not specify any restrictions on how the procedure will implement the use of FORALL. It does not say that no other code can be written, no other collections can be defined and used. So from that standpoint, almost any choice (except something on the order of "You will not be able to use FORALL in this scenario.") would need to be scored as correct. Which we did.

So I do not see why the VALUES OF choice would need to be scored as incorrect.

Other comments and my responses:

"1. The question refers to "that table" but didn't mention a table beforehand. 2. The word "element" might be misinterpreted as referring to either the indexes or the values in the associative array."

My response: yes "that table" was a typo. I will fix it, but I don't think it justifies a rescore. Element and index values: these two terms are used by Oracle in its documentation. An element is never used to refer to the index value, that I know of.

"In the PL/SQL Quiz from 2011-09-28 you stated:"I must write a procedure that accepts an associative array as a parameter, each of whose elements contains the primary key of a row in that table...". This is a litle bit confusing for me, because I took into account the possibility, that primary key may also consist of multiple columns, not only single column. Unfortunately, the question was for me not so clearly defined... Please, could you explain, which solution would be correct, if there is a multiple columns primary key"

My response: another very excellent point regarding the potential drawbacks of an "only words" quiz! Yes, I did not think about multiple column primary keys. That would certainly affect and complicate the implementation of the procedure (assuming it was a collection of records, you would have to write very different code in pre-Oracle11g versions, since you cannot reference a field of a collection-based record inside a FORALL until then), but my same point applies from above: I do not specify any restrictions on how the procedure is implemented, except that it use FORALL. So the correctness of choices would still not change.

11 comments:

Though I already submitted my feedback, I would just add that, since many players raised the same objection about the VALUES OF choice, it is worth to reconsider and listen however to their voice.

As we have seen for a long time, "words only" quizzes always raise issues that are hard both to prove and to contradict, each side has its own truth.

It is right that the quiz did not specify how it is going to implement the FORALL,but, however, the first choice clearly stated that "additional code should be used to create a dense array from the sparse one", so one would entirely legitimately expect that the two choices of INDICES OF and VALUES OF would be interpreted IN THE SAME TERMS, that is, with ONLY the amount of details specified in the quiz and the choices themselves.

Anyway, from the knowledge point of view, it would have been much more interesting to useCODE instead of WORDS and have the VALUES OF choice as an incorrect one.This would have left place for those who know the difference between the two to be rewarded according to their knowledge.If both these choices are considered correct, one could never know whether somebody marked both of them consciously of the additional code required (by ONLY one of them !) or simply out of NOT KNOWING EXACTLY which one is the correct one.

It would have suffice to just add to the quiz a phrase like "each choice represents a partial step for a solution", or something similar, but the "lack of simmetry" between the wording of the choices makes the scorring of the last choice very objectable ...

Unfortunately, these "wording" quizzes do not "fold themselves well" for being answered by simply YES or NO answers, an additional clarification is always required for understanding what exactly is being asked.

It is a completely different thing to err in the answer of a quiz out of lack of knowledgeor to err just because you have answered a COMPLETELY DIFFERENT question from the one that the author maybe intended to ask, but ultimately did not ask it the way he intended.

Of course the decision of rescoring does not pertain to the players, but accumulating "lost points" out of such misunderstandings is an undeserved penalty and I am sure that many players feel the same about it.

I thought that the 4th answer is incorrect, because of the first answer, where you explicitly specify that additional code is needed to densify the collection. And in the 4th answer you don't specify that any additional code in needed before you can use the FORALL.

So I think that a rescoring is needed in the way that everybody gets a correct score for the 4th answer.

As stated in the instructions, each choice is to be considered independently of the others. So just because one choice mentioned specifics of writing code, that does not mean that the same must apply to another choice.

Regarding "word" quizzes: I have offered quizzes in the past that were on this sort of topic and were code-based, and will likely do so again (ah, the demands of a daily quiz!).

Leave the answer as it is. We cannot always "correct" answers later, just because someone understood it in one way while others understood it in another. If we would start that, pretty much all questions need changes.

The main part of the question is "Which of the choices describe my options for implementing this procedure?". The VALUES OF answer is a correct answer to that question, no matter how you look at it. Yes, you need to write extra code to do so, but there is no explicit instruction disallowing it. So I suggest you keep the answer as it is and the scoring as it is.

"Word" quizzes are also good and welcome if theydo contain "enough words" so that to make completely clear what is being asked and not leave place to interpretations that may lead to two opposite answers being both potentially correct or incorrect.

For example, the "word" quiz of Sept 26 regarding SQL%ROWCOUNT was very precise and clear and no one raised any objection :)

Probably "word" quizzes do not have "enough potential" for being raised much above this depth, without adding a "wording reviewers team" to the quiz reviewers one.

Yes, it is not an easy task to create a new quiz every day ... just as it is also not easy to play it every day being 100% focused.But we all seem to be "eternal students" and also like it :) :)

I thought this was one of the better questions I've seen - and looking back now I don't think you could have worded it any better.

I must say as soon as I saw the final option of VALUES OF I thought people would raise objection, and I hesitated selecting it even though I knew it was a perfectly valid answer.

It challenged our knowledge of PL/SQL. You had to describe the solution for the first idea of "densifying" the array, and the final option eked out those who really knew what VALUES OF is all about with the rest.

As for the PK comment, well if the question states the elements contain the primary key, isn't that asserting that there mustn't be a compound key?

Hello All,First of all, no one objected that it was NOT a good quiz, rather the opposite is true,it was a very good quiz, but, unfortunately,not worded exactly as it should have been for making things clear ...Several players relate the first choice to the 4-th, out of a natural tendency to expect "the same amount of detail" to be supplied or not supplied in each choice, and that's the whole point of the controversy here ...

Unfortunately, what Mark says above is completely FALSE !

For using VALUES OF you DO NOT need a densely filled array, you just need an array of PLS_INTEGER-s (dense or not) that contains as its element values the indexes of the bind array(dense or not) to be used in the FORALL dml statement.

So, Steven, you can see how a few missing words could turn the results upside down and rewardexactly the missing knowledge instead of the correct one :( :(

As I said before, the whole problem here wasthe wording, namely, the VALUES OF choice "had the same flavor" as the INDICES OF choice,therefore they were treated alike (as complete solutions by themselves) by most of the-- let's NOT ignore this fact !!! -- highly ranked players, and this should light on a red light regarding the incomplete wording ...

Again, the quiz by itself was excellent, it just missed that simple phrase that would have made scoring to really reflect knowledge andnot what could have been a simple guess.

Iudith, due to the lack of a "Mark" in this thread, I assume you meant me (Mike) and, yes, you are correct that the VALUES OF collection need not be densely filled. Instead of "need", I should probably have written "can use". However, it is really beside the point! To use VALUES OF, with the given requirements, you do need the extra step of populating a collection and why wouldn't you make it dense? Anyways, my wording was meant to relate two choices that are both correct in context of a given question, nothing else.

Steven, perhaps you could change the question to "I must write a procedure that accepts an associative array as a parameter, each of whose elements contains the primary key of a row in that table, may execute some code, and then uses FORALL to update each row identified by one of those primary keys.". That little peace would clearify the whole think.

For me (as a native german) the question was "a procedure that accepts an associative array as a parameter":PROCEDURE QuitSolution (pKeys IN TtblKeyValues) IS

"... and then uses FORALL to update each row identified by one of those primary keys":BEGIN FORALL .... UPDATE ....;END QuitSolution;

and the first anwer widens the question that additional code must be executed before the FORALL.

Hello Mike, All,Yes, I meant to answer your comment, sorry for having misspelled your name :(.

Anyway:1. My objection was related to the fact thatwhile the first choice clearly specified not only that additional code is required, but even what is that code supposed to do, the choices of INDICES OF and VALUES OF did not specify any addition.And, as you said by yourself, some players even "owe" their marking of VALUES OF as correctto a wrong association with the first choice ...

2. In fact, the difference between INDICES OFand VALUES OF would have been much better emphasized by making the VALUES OF a WRONG choice instead of an objection prone correct one.

The whole point about VALUES OF could and should have been to test whether the players know that we CANNOT use this option with just the single parameter array passed to the procedure, as we CAN DO with INDICES OF.

In the way it was presented, it DID NOT check players' knowledge and awareness of the difference, but rather accepted it "as is" as a valid choice, which DOES NOT certify any knowledge without the additional code.

Keeping this quiz a "word" quiz, the best way should have been:1. to specify that INDICES OF and VALUES OF are supposed to be used alone (without any additional code)AND2. to consider INDICES OF as a correct solutionand VALUES OF as an incorrect one.

This would have made the quiz perfectly clear, all the choices consistent, supplying all the detail required for an exact understanding, without disclosing any information that could have helped players in answering, and, what is the most important, WOULD HAVE CORRECTLY EVALUATED PLAYERS' KNOWLEDGE, without scoring in fact their interpretation of the 4-th choice in one way or the other ...

With or without a decision to rescore, this is the truth about this quiz, and I am pretty sure that if in a hypothetical scenarioI or anybody else suggested these changes for the quiz before it was played, they would have been accepted as an improvement and probably spared much of this blog thread ...