The 25 February quiz touched on an aspect of Oracle database behavior that I suspect many players and more generally many PL/SQL developers were unaware (it took me by surprise!):
Oracle may, and often does, restart a DML statement after it experiences an internal failure. In such situations, Oracle automatically performs a rollback to an implicit savepoint it set before the statement executed. It then executes the statement again.
Two players wrote with an objection regarding the assumptions for the daily quiz and the reason for these restarts. Their comments below:
"After seeing that this question was ranked as advanced, I stared a long time at the code and really could not find the reason for it. One of the PL/SQL Challenge general assumptions says the following: "All code in the question and in the multiple choice options run in the same session (and concurrent sessions do not play a role in the quiz unless specified)." The write consistency issue which is explained magnificently by Tom Kyte (and I had the rare occasion to attend a live conference about it !) IS ALWAYS RELATED TO CONCURRENCY, which means AT LEAST 2 SESSIONS active !!! The Oracle documentation quoted in the Overall answer also speaks about A CONCURRENT UPDATE OR DELETE. So, under the quiz assumption that each piece of code (for each choice) IS NOT influenced by any other session, the choice that says that the package variable can end by being HIGHER than the c_iterations constant WAS WRONGLY SCORED AS CORRECT !!! It is true that placing non-transactional code in triggers is in most cases NOT a good practice and prone to bugs, like for example, code being re-executed an additional time unintentionally after a REAL rollback of a transaction (due to some unhandled Oracle error or an explicit ROLLBACK, and NOT because of write consistency issues !!! ) but THIS IS NOT THE CASE in the "sterile single session" context of the PL/SQL Challenge ... It's true that following this consideration the whole quiz "will lose its point", but I think that the assumptions rules fully justify a rescoring for this quiz."
and
"A quibble more than a bug with yesterday's quiz... One of the assumptions of the quiz is that concurrent sessions don't play a role unless otherwise specified. The text of the question about what *could* be displayed strongly implies that you intended to point out that a trigger may be executed more than c_iterations times in order to ensure write consistency. But I'm not aware of any way to cause that to happen without positing the existence of another session that is accessing the PLCH_EMPLOYEES table and the text of the question does not explicitly specify that we may consider the impact of other sessions."
My response: it is certainly possible that this error occurs most frequently because of concurrency issues, but I can tell you for certain that it is not the only cause. When my reviewers and I ran tests of this code (as we did that a lot), we saw restarts happening when there was not any concurrent behavior. None at all. I sat here on my lonely, little laptop - the only user on the instance, nothing else playing with the plch_employees table, and I consistently saw restarts.
So this issue is not always related to concurrency and I think the current scoring stands. As to why these restarts happen without a concurrency issue, I have no idea and it seems that Oracle is mum on the topic as well.
Your thoughts? The results of your own tests? Can no one else run the verification code and see re-starts, without a second session running and touching the plch_employees table at the same time?
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.
26 February 2011
22 February 2011
Nuances of NULLity (2026)
In the 21 February quiz, we asked:
Players wrote with the following concerns and my responses:
1. "I think there was a slight mistake in this quiz...it is hard to see the diff between '' and ' ', but this makes big difference in result. see SQL below. and results. That's why I didn't choose 3rd answer correctly. select replace ('abc#def#', '#', '') from dual; Result: abcdef select replace ('abc#def#', '#', ' ') from dual"
SF: Well, I don't think this was a mistake, per se, but I can see how it might have been difficult to tell. I am going to add to my list of "cautions" when writing and reviewing quizzes that we should avoid relying on the appearance of white space in our quizzes.
2. "It was asked to replace all occurrences of # with NULL. I wasn't aware that an empty string ('') was the same as NULL, While I understand what the spec says, (NULL or empty string will replace all occurrences, is it really equivalent to say it is setting it to NULL?"
SF: ah, yes, the old "NULL vs. zero-length string" issue. There are many viewpoints on this (see this thread for such a discussion, including a fine contribution by Justin Cave, winner of 3rd place in most recent playoff). The bottom line is that Oracle treats these as the same today and very likely always will.
[Side note: the link to Justin's name is the first time I have taken advantage of our public profile URL in a blog. :-) I hope you are doing the same, on your own blogs or websites. Give everyone an opportunity to learn about your PL/SQL career and your activity on the Challenge!]
3. "I would like to share with you that I just got (maybe too) suspicious because of the evil word 'NULL' and your formulation of the question as 'Which of the following uses of REPLACE will replace all the # symbols with NULL?' How can you tell if a certain character is being replaced by a null? All we can we see is the output. So why not ask: which of these usages would lead to 'abcabc' being printed. Or: which of these usages would lead to removing all of the '#' occurrences from the input string. Well never mind. Other developers probably do understand the question." and similarly "On today's quiz, I assume "replace all # characters with NULL" means "remove all # characters from the string, leaving the other characters." But this wasn't completely clear to me."
SF: Yes, I think your formulation is better - and I also need to be more careful about referring to NULL as opposed to "the NULL value" - but I don't believe that any changes in scoring is required.
4. Finally, a reviewer and player both noted that I could have (and felt that I should have) included another choice:
SF: Ah well, that would have been good to do, you are right. I will create another quiz on this particular nuance of REPLACE!
Your thoughts, dear players?
Players wrote with the following concerns and my responses:
1. "I think there was a slight mistake in this quiz...it is hard to see the diff between '' and ' ', but this makes big difference in result. see SQL below. and results. That's why I didn't choose 3rd answer correctly. select replace ('abc#def#', '#', '') from dual; Result: abcdef select replace ('abc#def#', '#', ' ') from dual"
SF: Well, I don't think this was a mistake, per se, but I can see how it might have been difficult to tell. I am going to add to my list of "cautions" when writing and reviewing quizzes that we should avoid relying on the appearance of white space in our quizzes.
2. "It was asked to replace all occurrences of # with NULL. I wasn't aware that an empty string ('') was the same as NULL, While I understand what the spec says, (NULL or empty string will replace all occurrences, is it really equivalent to say it is setting it to NULL?"
SF: ah, yes, the old "NULL vs. zero-length string" issue. There are many viewpoints on this (see this thread for such a discussion, including a fine contribution by Justin Cave, winner of 3rd place in most recent playoff). The bottom line is that Oracle treats these as the same today and very likely always will.
[Side note: the link to Justin's name is the first time I have taken advantage of our public profile URL in a blog. :-) I hope you are doing the same, on your own blogs or websites. Give everyone an opportunity to learn about your PL/SQL career and your activity on the Challenge!]
3. "I would like to share with you that I just got (maybe too) suspicious because of the evil word 'NULL' and your formulation of the question as 'Which of the following uses of REPLACE will replace all the # symbols with NULL?' How can you tell if a certain character is being replaced by a null? All we can we see is the output. So why not ask: which of these usages would lead to 'abcabc' being printed. Or: which of these usages would lead to removing all of the '#' occurrences from the input string. Well never mind. Other developers probably do understand the question." and similarly "On today's quiz, I assume "replace all # characters with NULL" means "remove all # characters from the string, leaving the other characters." But this wasn't completely clear to me."
SF: Yes, I think your formulation is better - and I also need to be more careful about referring to NULL as opposed to "the NULL value" - but I don't believe that any changes in scoring is required.
4. Finally, a reviewer and player both noted that I could have (and felt that I should have) included another choice:
BEGIN DBMS_OUTPUT.put_line (replace ('abc#def#', '#'));
That is, test your knowledge of the impact of relying on the default value for the third argument.SF: Ah well, that would have been good to do, you are right. I will create another quiz on this particular nuance of REPLACE!
Your thoughts, dear players?
Subscribe to:
Posts (Atom)