06 August 2010

Only use code-based quizzes?

I received this email today from a player:

I think that the questions should be just only code based and should not haven anything that can be left to interpretation. For example, in yesterday's question (05-AUG-2010) I knew the correct answer, yet, I got two options wrong. Why? Because it was written in natural language instead of being just code based interpretation. In my point of view, the answers had one crucial difference, that could have confused the players (it confused me... :( ). When the VALUE_ERROR is raised on the block declaration, it could never be handled on the block level. Yet, the other two options are raised block level and, if left unhandled by the programmer, they are propagated to the outer block and so on. So they were all raised to the outer block, bu it is my belief that internally, the exception is always handled block level except when it is in the declaration... You may be thinking that the exception is certainly not handled inside the block, but I think that things are not so black and white in this subject. See this example:

   l   NUMBER;
      DBMS_OUTPUT.put_line ('Mayday, mayday, I don''t know what to do...');

Was the exception VALUE_ERROR handled or not? Certainly it was caught... But then it was re-raised (or propagated??) tho the outer block... And nothing relevant was done... Probably I wouldn't remember this if there wasn't one option that was clearly different from the others in a feature relevant to the answer... If I didn't know that I couldn't handle an exception raised in the block declaration on that block, I would most probably have a higger score. If all the options were written in PL/SQL and the question was "Which of the options have the following output?" it would be easier to prove each one's knowledge in PL/SQL...

Here is my response:

I agree about offering lots of code-based quizzes, and that is certainly what you have seen in the PL/SQL Challenge. Having said that, I do think there is significant value in also tackling quizzes that ask you to understand concepts and rules in PL/SQL, and that pretty well means doing it in English, for the most part.

Regarding this player's specific objection, I don't really "get it." The choice to which I think he is referring as a source of ambiguity says:

"The exception section has only one WHEN clause (handler), and it is for NO_DATA_FOUND."

so the scenario in the code above does not apply. Certainly, if you have a WHEN OTHERS, you can trap and handle the exception, at which point you could choose to re-raise it. That is not, however, a choice I offered to you.


  1. The player who raised this concern doesn't have a blogspot account so he replied by email. Here it is:

    I think I didn't explain my self in the best way... There was one thing in what I said that was the most important part and got lost in the middle of the text."(...)it is my belief that internally, the exception is always handled block level except when it is in the declaration.(...)"

    >From Oracle documentation (http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/errors.htm#i3365):
    "When an exception is raised, if PL/SQL cannot find a handler for it in the current block or subprogram, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. If no handler is found, PL/SQL returns an unhandled exception error to the host environment."
    "Handlers in the current block cannot catch the raised exception because an exception raised in a declaration propagates immediately to the enclosing block."

    >From these two paragraphs what I understand is that internally, one exception is always handled at the current block level (at least to find the programmer's defined handler) and if no handler is found, then it is raised to the outer block, unless it is at the declaration part of the block, in which case it is immediately raised to the outer block.

    Do you think this interpretation is wrong?

    >From my interpretation (the example code I provided tried to "emulate/explain" this behaviour), I find my answer to the question to be correct. While I understand your answer and I think it is also correct (maybe you don't agree with me or you didn't anticipate my interpretation), my point is that with code examples, the question wouldn't be subject of interpretation, it would (or at least should) be clear as water. With textual questions, there is always too much left to the readers mind.

  2. In fact, I do think this interpretation is not correct. Oracle states that if it cannot find a handler, the exception propagates out of the block, unhandled.

    The exception is not handled in every block; instead, Oracle tries to handle the exception.

    I don't see a reason to interpret these statements from the document as "exception is always handled at the current block level."

    Does anyone else see things differently?


  3. Predominantly using PL/SQL code based questions would seem to have one significant benefit: it would minimize the effect of English language questions on those players for whom English is a foreign language. I have great admiration for those players who have performed competently despite any language barriers. Basing the quiz solely on code, however, would ignore other important aspects of PL/SQL proficiency. The ability to determine the behavior of a piece of code is not the same as being able to create the code. Novice programmers, and even some non-programmers, may be able to ascertain the operations performed by a section of code; however, that does not indicate a strong grasp of the underlying concepts. It is generally much easier to recognize concepts than it is to apply them.

    For the quiz of August 5, neither the question nor the choices were ambiguous. The Oracle documentation is quite good with regard to exception processing and handling. As quoted previously, "When an exception is raised, if PL/SQL cannot find a handler in the current block ... the exception propagates" (emphasis mine). If there is no handler then an exception cannot have been handled.