You feedback needed: how best to show "you got right/wrong" info for past quiz?

In response to player requests, in the 1.7 upgrade of the PL/SQL Challenge, we now display for each quiz choice detailed information about how you did compared to other players.

The text will look like this:

You got it right
76% got it wrong.

or

You got it wrong.
42% got it right.

In other words, we display the % of people who answered differently from you. I thought this would be a nice way to highlight your relative performance.

One player, at least, does not like this, writing: "While I was going through the results, I noticed that the statistics on how well the others are doing (23% of all players got it ....) sometimes says 'wrong' and sometimes 'right'. It would be more consequent if they always say 'right' (or 'wrong' if you prefer that option). Now I must do calculations in my head (arghhh...) after reading the text and interpret what was the sentence to compare how well the others did on the answer and to see how stupid I was in selecting the wrong answer compared to the others."

Does anyone else have any strong (or otherwise) opinions about this?

Answer to 5 November Quiz is Wrong (1621)

The 5 November quiz tested your knowledge of the way that the value of the SQL%ROWCOUNT attribute is set. Vitaliy wrote to me that while the quiz was scored correctly, the explanation of the results are wrong: 1. Until a SQL data manipulation statement is executed, SQL%ROWCOUNT yields NULL. Not zero. 2. SQL%ROWCOUNT=0 in your code because the COMMIT changes the SQL% attributes (reset rowcount to 0). (JFYI, in 7.3 commit does not reset SQL%ROWCOUNT to 0 and when upgrading some people had trouble with it). Vitaliy then points us to the Oracle documentation: "The value of the SQL%ROWCOUNT attribute refers to the most recently executed SQL statement from PL/SQL....The SQL%ROWCOUNT attribute is not related to the state of a transaction. ...Also, when an autonomous transaction is exited, SQL%ROWCOUNT is not restore to the original value in the parent transaction." I will change the text of the answer so that it is accurate. My apologies for this - but of course happy as always to be corrected! SF

The quote character (q) and the 4 November quiz (1604)

This quiz tested your knowledge of the new-to-10g feature of using the q character so that instead of doubling each single quote inside a string literal, you can specify your own delimiter character for the literal, and then use single quotes inside the string, thereby making it easier to read and manage dynamically constructed strings. I received several emails with highly varied responses to this topic, some indicating what I can only consider are bugs in this feature or in the way they were used when players tested the feature. I have had no problems with using the q character on both Oracle10g and Oracle11g databases. 1. "I loved today's question, I was not aware of the q function...and I could immediately use it today, so another victory for the Challenge!" 2. "Something strange happens related to this quiz, regardless of the choices presented. It looks like, in general, the alternative quoting mechanism does not work at all if the literal string contains a SINGLE QUOTE character as the first character of the literal value. This restriction IS NOT documented in the Oracle SQL Language reference, however it gives the following error: SQL>select q'*'Hello,' said the man, who didn't like goodbyes.*' 2 from dual; ERROR: ORA-01756: quoted string not properly terminated though, as per the documentation, this was supposed to work. Removing the first quote character and leaving all the alternative delimiter stuff in place makes things working. The case is the same for both SQL and PL/SQL statements. The documentation only says that: "...if the quote_delimiter appears in the text literal itself, ensure that it is not immediately followed by a single quotation mark." which IS NOT our case. This practically renders ALL the choices to this quiz as incorrect, except for the one that uses the classical quote delimiter, though I am sure that this WAS NOT your intention, you probably intended to check the knowledge about the "alternative quoting" feature, and not to make all of us stumble upon this documentation bug ... I dare say that: 1. Most of the players were not aware of this restriction regarding the first character in the literal. 2. It could be interesting to know what percent of people are using this mechanism at all in their daily work ... I am sure that many other players will comment on this quiz." 3. From Christian Rokitta: "I just published a blogpost on today's quiz subject" 4. "Thanks for bringing this topic up. I did not know that Oracle had introduced this ability. (Thus I got a very poor score in yesterday's quiz) I tried it out immediately - but your examples did not work on my 11g database (I know, the quiz assumes a 10g database, but features introduced in 10g should also be available an work in 11g also). Playing a bit around I found out that the user-defined delimiters works when there are a even number of quote signs in the string literal, but not when the number of quote signs is uneven, as in your examples. I wonder, is this a bug in 11g?" 5. "This is a new one for me, the Q mechanism. Had to quickly search for the documentation and made one mistake, the () I oversighted. But is the sentence correctly English? The comma should not be there?" Regarding #5: Hmmm, you know, I think you are right. I am used to putting a comma before and after the speaker of a quotation, but perhaps in this case the "who" should flow directly after the "man". I will check with my wife, who has a degree in English literature. I am puzzled by the reports of this functionality not working. Again, I did not experience any problems. Perhaps you could use the verification code on the Past Quizzes page and see if, indeed, my precise code in the choices are causing you errors. Let us all know what you find out.

On Winners and Adjustments for the end of October

You will find on the PL/SQL Challenge a list of the most recent winners. I ran into an interesting and new circumstance for October that I wanted to highlight for you.

At the end of each month I account a winner of the "most correct" raffle. You are eligible to participate in this raffle if your correctness over the month was at least as high as the #1 ranked player -regardless of the amount of time it took you to answer the quizzes.

When I ran my script to pick this "most correct" winner for October, my code raised a NO_DATA_FOUND exception. After a quick debug session, I discovered the reason:

No other players matched the 100% correctness ranking of Eurico Matos in October 2010!

Achieving 100% correctness for an entire month is quite an achievement in general, and even more so when no other players were able to do so. Congratulations, Eurico!

I ran the adjustment algorithm for very fast answer times this weekend as well, so some of you might notice a sudden downward shift in your rankings. Remember: take your time, read carefully and thoroughly.

Cheers,
Steven Feuerstein

A better alternative to NVL2 (29 October quiz) (1565)

The 29 October quiz tested your awareness of the NVL2 function (which works quite differently from NVL), and the fact that it is available only in SQL, not yet in the PL/SQL language. The quiz offered several options that served, in effect, as alternatives to NVL2, from within PL/SQL. Vitaliy wrote to suggest that I had missed a simpler and more elegant alternative: CASE. So just in case you would like to use it....

CREATE OR REPLACE FUNCTION NVL2 (val         IN VARCHAR2
                              , ifnotnull   IN VARCHAR2
                              , ifnull      IN VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  RETURN CASE WHEN val IS NOT NULL THEN ifnotnull ELSE ifnull END;
END;
/