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"."