BEGIN
FOR r_managers IN (SELECT * FROM plch_employees)
LOOP
UPDATE plch_employees
SET salary = salary * 2
WHERE employee_id = r_managers.employee_id;
END LOOP
COMMIT;Notice there is no semi-colon after END LOOP. Many players submitted a bug report about this, but it is not a mistake in the quiz. Instead, since there is no semi-colon, what appears to be a "COMMIT;" statement is actually treated by the PL/SQL compiler as the label for the END LOOP statement.When Sergey submitted this quiz, my first reaction was to say "Nah, that's just a trick; what does it teach you?" But after looking at it more closely and reading Sergey's explanation, I decided there was a useful lesson or two here:
1. Challenge your assumptions: this seemed like a classic example of a scenario in which your code is not compiling or there is some other bug, and you stare and stare at the code and cannot see the problem. Usually this is because you make an assumption, it goes unexamined, unrecognized, and it is wrong.
2. Use your IDE formatters! Using Toad, SQL Navigator, PL/SQL Developer? They all have powerful formatters to help you read and understand your code. When I reformat the above code, it turns into:
BEGIN
FOR r_managers IN (SELECT * FROM plch_employees)
LOOP
UPDATE plch_employees
SET salary = salary * 2
WHERE employee_id = r_managers.employee_id;
END LOOP commit;
And now I can see clearly the problem.So I am very sorry that some of you felt tricked (see comments below), but (a) I do think it was an interesting and worthwhile exercise and (b) remember, it's just one of many and I truly doubt they'll be many like this!
Comments from Players
"I would have rated this an "Advanced" question - based on the results it is obvious not many people (myself included) knew that you can omit the label before the loop. Myself, I would probably have interpreted the missing semicolon as a typo. If I'd known the difficulty level was not Basic, I would have looked for more than just "what is the result of COMMIT and ROLLBACK within a procedure"."
"I think deliberately missing a semi-colon to give unexpected result, without making it obvious that had been missed is very much cheating. As is rather obvious from the ludicrously low proportion of people who got it right. And I expect most of te people who did get it "right" actually did so for the wrong reason."
"The quiz on January 28, 2011 was more of a "trick" than a test of one's PL/SQL knowledge. The code was intentionally formatted to obfuscate its intent. I didn't notice the missing semicolon, and thus answered incorrectly; however, it has been stated repeatedly that one should not expect such trickery from the PL/SQL Challenge. I am extremely disappointed that such a question was accepted."
"I could imagine myself happening this to me in real life while debugging a code and me spending an hour or so to figure out what was going wrong. And finally submitting this as a quiz on PL/SQL Challenge. And getting delighted to see only 7% of all players could get it right)). To my opinion this quiz tests more of your debugging skills than PL/SQL knowledge. I am not complaining in any way but saw this type of quiz first time on PL/SQL Challenge so thought of sending a note. I would be more careful in reading the code now onwards PS: When I encountered this quiz it look so straightforward to me that in spite of knowing the correct answer (which was wrong of course) I tempted to actually write a sample code and test it before submitting my answer. I did that and confirmed that my answer was indeed right but it was wrong of course because I tested my sample code with semicolon on."
"While I'm not really in a position to cry foul with the 28/1/11 quiz, it does seem a little against the typical tone of the quizzes. Sure, there is a lesson to be learnt here, but I'm not entirely sure this was the way to teach it. No doubt you will get a few comments about this particular quiz, I'd be interested on your thoughts."
"Very un-funny trick in the quiz. You "forgot" the semi-colon after "end loop" so the compiler considers the commit a loop label. After all, "COMMIT" is not a reserved word as you have previously pointed out. I guess you wanted to check who would actually know and understand this feature. I can see how it could be a devious problem to debug. In a timed competition, however, I would have to call the question unfair. It seems particularly so since someone who truly does not understand transaction control might choose this answer because they are "double wrong." I enjoy your competition highly and appreciate that you put in all the effort it must take. I feel questions like this taint the experience. I look forward every week day to the Challenge. I hope to be able to continue for a long time to come."
