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.
04 September 2010
Players cry "No choices right!" for 3 September quiz(1370)
The 3 September quiz regarding use of the RAISE; statement includes this paragraph:
Each of the choices below contain code to create a procedure named proc2 (and in some cases, another procedure named show_error). After executing the code in each choice, I then want to run the following block and see an unhandled VALUE_ERROR exception ("ORA-06502: PL/SQL: numeric or value error").
Several players wrote to say that none of the choices could be correct because all of them could only display at most the error code, but not the error message, since I called SQLCODE and not SQLERRM.
That is true, but what I stated was that I want to "see an unhandled VALUE_ERROR exception."
I then included the full message text in parentheses to be clear about how the VALUE_ERROR exception (defined in the STANDARD package) would appear as an unhandled Oracle error.
I did not ask "What text will be displayed on the screen?"
So I believe that the quiz was scored correctly and no changes will be made. Your thoughts?
Subscribe to:
Post Comments (Atom)
Hi Steven,
ReplyDeleteWhen I was thinking about choosing the right option I was sure that the point would definitely arise.
I had two confusing option: 1) to consider 3rd option as correct, but I was not sure if that question was also related to choosing SQLERRM/SQLCODE for logging exception info
2) to consider it as incorrect, but was not sure if "to see" means "to get" an exception.
Moreover exception displayed by tools like SQLPLUS, TOAD etc is not the same as a result of PL/SQL block that actually doesn't display that error.
In both ways I didn't have 100% guaranteed answer and that's why I had to select just 1 option and I was unfortunate.
I think that is not good that the right option depends on unclear implications.
Kind Regards,
Sergey Porokh
Sergey,
ReplyDeleteI am sorry that some of the words I used led to confusion.
Let's see what we can learn from this experience to help answer future questions.
You say "was not sure if that question was also related to choosing SQLERRM/SQLCODE for logging exception info".
It is extremely important when taking the quiz to not "read into" the question any more than what is presented. I did not talk about logging the error information. Instead, I specifically talked about "seeing" an unhandled exception.
I don't how else one would most naturally interpret "to see" except that you run the block and see what happens: "Oh! That exception went unhandled!"
Looking at the choices, though, I realize that what could have caused confusion was the use of DBMS_OUTPUT.PUT_LINE to display the error code.
The screen output does not play a role in the quiz, and I should probably not have included those statements.
Cheers, Steven
This was a great question - almost tripped me up :) but it was fairly obvious to me that the question was about where a RAISE; is allowed, not about how the error is displayed on the screen.
ReplyDeleteI found your post-question comments interesting - particularly:
"And, certainly, if you try to compile a RAISE; statement in a separate procedure, with the intention of creating a nice, reusable "log and re-raise" utility, Oracle will not let you. That is very unfortunate and hopefully they will relax this restriction in the future. All they have to do is shift the check for "RAISE; only valid when an exception has already been raised" to run-time instead of compile-time!"
+1 this. I'd like to have code like this:
DECLARE
PROCEDURE log_and_raise
(the_exception IN EXCEPTION)
IS
BEGIN
dbms_output.put_line(SQLERRM);
RAISE the_exception;
END;
BEGIN
...
EXCEPTION
WHEN OTHERS
THEN
log_and_raise
(the_exception => PLSQL%EXCEPTION);
END;
Yes, Jeff - that is another fantasy/dream of mine. Imagine: pass an exception as an argument! Treat it as a variable just like any other kind of data...sigh...I get the feeling we will have to wait a LOOOONG time before we see that.
ReplyDeleteSF
Well Steven,
ReplyDeleteI do agree that if you present a questioin, there should be only 1 correct answer and by providing the sentence "see an unhandled VALUE_ERROR exception ("ORA-06502: PL/SQL: numeric or value error")" I also get thrown off because in IT correct is correct. I did not see the unhandled exception "("ORA-06502: PL/SQL: numeric or value error")" only the code. So in this case you must give us the benefit of the doubt. You must also understand that as NOT (mothertonque) English speaking people we cannot sense the nuances in the English language as you can.
So again, we have the benefit of the doubt.
greetings
danny Ven
Danny,
ReplyDeleteYou write "if you present a question, there should be only 1 correct answer." That is simply not the case in the PL/SQL Challenge. There can be more than one correct choice.
I don't understand what you mean by "I did not see the unhandled exception "("ORA-06502: PL/SQL: numeric or value error")" only the code." True, you are just looking at the code, but you need at a minimum to visualize what will happen when the code is run, or write yourself a little block to test your understanding.
You write "So in this case you must give us the benefit of the doubt." If you mean I should give you credit, no, I am not rescoring in this case. I do think it was clear enough, even if English is not your first language.
Again, what else can it mean to "see an unhandled exception" except that when you run the code, an exception comes out of the block unhandled?
Regards, SF
Hello Danny,
ReplyDeleteI do agree with Steven that this particular case was not related English language related misunderstanding.
My confusion was related to
1) using dbms_output that doesn't display ORA message in the 3rd option.
2) usage of word "to see".
Yes we can "see" just a code, i.e. SQLCODE displayed by dbms_output.
But the text of exception is printed by environment where this code is run (SQLPLUS, TOAD)... it still seems to confusing for me and not really correct, but I cannot find right arguments to prove it :)
That is why I can accept that it should not be re-scored.
However, that doesn't mean that I am very happy with this decision.
Kind regards,
Sergey
I cannot agree more on this. Once again I am surprised there are complaints. With or without the DBMS_OUTPUT lines, it was perfectly clear what the question was. Seems again you cannot please everyone all the time...
ReplyDeleteToine