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.

02 April 2011

Ambiguous wording in 1 April quiz? (2145)

The 1 April quiz offered this question:

Over the years, many development teams have created large scripts in anonymous blocks to run in the background and perform all sorts of complex data management tasks. Which of the choices describe a reason to move as much code as possible from the anonymous blocks into procedures and functions stored in the database (either schema-level or within packages)?

Several players raised a concern about the scoring of this choice as correct:

The business logic in the scripts is more likely to be reused.

Here is a comment that reflects the concerns:

You marked the "The business logic in the scripts is more likely to be reused." as a correct answer, but I have great doubts about its wording. From my point of few this wording should be interpreted as "business logic that stay directly in the scripts (as oppose to be moved to stored objects) has more chances to be reused". Definitely incorrect statement.

I would agree with this objection, if I had to decide on the correctness of this choice separately from the question. All by itself, "The business logic in the scripts is more likely to be reused." can be interpreted to mean either that the logic is more likely to be reused when left in the anonymous block OR when it is moved to a stored program unit.

But when playing the quiz, you do not consider these choices out of the context of the question, and the question clearly asks "Which of the choices describe a reason to move as much code as possible from the anonymous blocks into procedures and functions...?"

So I believe that the wording of the question removes any possible ambiguity - no change in scoring is required.

Another player wrote: "Can an anonymous block be optimized to the same degree as a stored procedure? The first thing that came to mind seeing that option was that an anonymous block cannot be natively compiled."

My response: I am actually not certain about what Oracle will do with anonymous scripts when native compilation is enabled, but I feel that when you are working with a language whose compiler offers automatic optimization (true of Oracle PL/SQL since 10.1), a statement like "The compiler will optimize the code in the stored program units, but it cannot do the same for an anonymous block." would most obviously and clearly relate to that optimization process and not a special feature of the Oracle Database like native compilation.

10 comments:

Hello All,I think that a better wording for the above choice in discussion would have been:"The business logic in the scripts is more convenient to be reused." or even"The business logic in the scripts is easier to be reused."

Using "more likely" might rather been interpreted as something saying "ok, if it is in a script I WILL NOT use it, while if it is in a stored subprogram then I will".

The truth is that if a piece of business logic IS REALLY NEEDED, then it WILL be reused, whether in as script or not and, hopefully,it will be identified at least when needingto be reused, as a good candidate to be moved and stored in the database.

I rather wonder why is it that the compiler warnings are NOT generated for anonymous blocks.I think that just as pl/sql optimization DOES HAPPEN for anonymous blocks exactly as for stored subprograms, warnings should have been generated as well, just like compilation errors are, in fact, the compilationof a pl/sql unit happens in the same way,peformed by the same compiler.

I think that the real drawback of the pl/sql warnings in general is that they are not set on by default, as the optimization level is.This is why probably this feature is used lessand thus we lose a tool that could be usefulfor improvement.Also, as much of pl/sql testing is done in anonymous blocks, prior to moving that code to the database, implementing warnings for anonymous blocks would have been a useful feature, so maybe out there at Oracle they will start thinking about it ...

By the way, while the Oracle10g documentation explicitly specifies that pl/sql warnings are NOT generated for anonymous blocks,Oracle11gR2 just implies it, by saying that"when compiling stored pl/sql units the compiler generates warnings" ...Unfortunately, not the only place where documentation seems to become shorter and less explicit in the later versions ...

"The business logic in the scripts is more likely to be reused." cannot be used as a valid reason to move code out of the scripts,as it is wrong by itself. It shall be said "The business logic in the stored procedures is more likely to be reused then in the scripts".

Compare to "Because blue light has longer wave length then red one the business logic shall be moved to from the scripts to the stored procedures".

Hi, I also interpreted the choice wrongly and therefore made a mistake. And I think that you are right and it was technically correct a I should definitely consider an answer choice in the context of a question. However I also think that the creators should try to avoid such situations.

If I remember right then you, Steven, set your own guideline of not creating negative questions to avoid these potentially confusing situations. This combination of Q&A in my opinion violates this guideline and has the potential ("proved" by players results) to misguide.

On the other hand ... Even though I occasionally disagree with your reasoning I do have an enormous respect to what you have created here.

Within the context of the quiz, I correctly inferred the response to mean "The business logic in the scripts is more likely to be reused [if it is moved to stored procedures]." However, as originally written, the option can reasonably be read as stating that business logic is more likely to be reused when kept in a script (which would not be a correct response). The following text would not be subject to the same ambiguity: "The business logic is more likely to be reused if it is accessible as a stored procedure." A change in scoring is warranted.

The choices are only available and can only be interpreted "within the context of the quiz." Your inference was not only correct; it also followed precisely how players are supposed (as specified in instructions and assumptions) to read, understand and respond to the quiz.

Yes, a few more words in that choice would have made the intention explicit (and I have made such a change), but I do not believe that a change in scoring should take place.

I interpreted the wording "The business logic in the scripts is more likely to be reused" as "The business logic moved to stored procedures is less likely to be reused, so it is a good reason to move it". That makes me take it as a joke (along with the quiz date ).