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.

31 August 2010

The 30 August quiz asked you to choose options that would stop a program unit from being usable if the optimization level for that unit was < 2 (the current default level of "aggressive" optimization).
One choice, scored as correct, was:

Several players raised a concern about this scoring, with one person writing:
"Hello, from my point of view today quiz contains a bug. The third choice should use not only NAME but as well OWNER in the WHERE clause [since it was a query against an ALL* view], otherwise it may fail even if the PLSQL optimization level for it is set to 2 (with TOO_MANY_ROWS). For this reason I have not selected it, while I assume it was intended as the "correct" choice."
And as another player put it: "To be pedantically correct, shouldn't it either use USER_PLSQL_OBJECT_SETTINGS or include the OWNER in the where clause? There might be an object with the same name in a different schema that current user has access to (and therefore included in the ALL_* view.) (But if such an object existed, I would get a runtime exception of too many rows, so the answer is still 'correct.' Anyway, the general assumptions of course tells me that there probably is NOT another schema with such an object, so I'm not asking for any rescoring - mostly that when you publish the answers you might make a note that when using ALL_* view it's normally prudent to include OWNER in the where clause."
Well, to tell you the truth, that last comment pretty much said it all. To summarize:
1. I should have included "OWNER = USER" in the query against the ALL_PLSQL_OBJECT_SETTINGS view. In fact, any query against an ALL* view should include a predicate in the WHERE clause for the owner - unless you really want to analyze rows across all schemas.
2. There is no need to rescore. As the second player explained, the assumption for the quiz is that the only DB objects that exist are those described in the quiz itself (or defined by Oracle with the installation of the database). So the query could only return one row.
3. I will change the choice to reflect this critique (add "OWNER = USER").
To conclude: no change in scores or ranks, but I will strive in the future to either use a USER* view or include an "OWNER=" predicate with an ALL* view.
Thanks for pointing this out!
Finally, another player had a very different concern: "The wording of today's quiz was really puzzling for a non-english like me. I'm not sure to have understood what was the question. Don't you think there was a simpler way to write the same sentence?"
My apologies; I do see what you mean. I need to step embedding sentences within questions, as I do below:
"Which of the choices define a program named "COMPUTE_INTENSIVE" in a way that ensures that the program cannot be used (it will either fail to compile or always raise an exception before the local subprogram body_of_proc is executed) unless the PL/SQL compiler optimization level for the program is set to at least 2?"
Thanks for reminding me about this. I will continue to try to simplify the language I use in the questions.
SF

4 comments:

Agreed. IMHO I have an excellent grasp of English, both grammar and speling :) - but I found I had to read and re-read that sentence quite a few times in order to parse it correctly.

The main issue for me was not so much the embedded sentences, but the multiple negatives - "cannot be used...", "it will either *fail* to compile OR *always* raise an exception...", "unless..." - if this question was written in pseudo code it might end up looking like this:

As an aside I am impressed at Stevens ability to adress so many various topics from all corners of PL/SQL in such a multitude of questions with so relatively few mistakes and ambiguities. When thousands of nerds all over the world are just waiting for the chance to nitpick, it's amazing so few problems there are.

In general when I'm slightly in doubt, I will try to use common sense and assume that Steven has not made a "very clever trap"-answer. I have seen "tricky" answers before, but almost always the "tricky" part have been unambigous. So if I spot ambiguities I tend to assume they were unintended by Steven :-)

An so in the interest of the quiz as a very educational experience, what I like to do (and hope others do as well) is to point out the minor points to Steven so his comments on the answers can add further value to the education of us all.So even though I do try to compete for points in the quiz, I would not want rescoring for minor ambiguities that often can be resolved by common sense - rescoring is in my opinion reserved for outright mistakes (which of course also can occur from time to time - we are all human :-)

How come that the second choice (the one that uses ALL_PLSQL_OBJECT_SETTINGS.PLSQL_OPTIMIZE_LEVEL) is marked correct when the given code obviously doesn't become usable even if you set PLSQL_OPTIMIZE_LEVEL to 2?

I'm a bit confused by the change to the scoring on this question. The second option cannot compile, therefore it is incorrect - even if the PLSQL_COMPILER_OPTIMIZATION is set to 2, it won't compile, therefore it is not at all usable. Why is it "correct"?