Change in policy for awarding prize for identifying mistake in quiz

On the Rules page you will find this:

If you are the first person to identify a mistake in a quiz that affects the ability of players to answer correctly (typos, for example, may not apply), then you will win an O'Reilly ebook.

I have had several complaints about this rule, along these lines: "You should do something about the 'first to report mistake.' I and most others in Europe are sound asleep, taking a shower, eating breakfast or driving to work during the first eight hours of a quiz day. The chance that any of us will be the first with anything is pretty slim."

My response has generally been unsympathetic: "If a European really wants to 'win' this prize (and some have), they should hover over their machines at midnight...."


But I have given it more thought and have decided that what is important to recognize and (potentially) reward is not that a person found the mistake "first," but that they are paying enough attention to the quiz to notice the problem.


So from now on I will award a prize for finding a mistake in my quiz by selecting a player randomly from all those who reported the problem before the quiz ends for that day.


Keep those ideas coming! The PL/SQL Challenge is so much better for all the feedback and critiques I have received from players over the last five months.


Thanks, SF

What is that POWER (2,32) all about in the 10 September answer? (1376)

In the answer to the 10 September quiz regarding constructors, I offered an example of a type that could you use to calculate elapsed time of your program execution, down to the hundredth of a second. Here is the body of the type:

CREATE OR REPLACE TYPE BODY timer_t
AS
  CONSTRUCTOR FUNCTION timer_t (self IN OUT timer_t, title_in IN VARCHAR2)
     RETURN SELF AS RESULT
  IS
  BEGIN
     self.title := title_in;
     RETURN;
  END;

  MEMBER PROCEDURE start_timer
  IS
  BEGIN
     start_time := DBMS_UTILITY.get_time;
  END;

  MEMBER PROCEDURE show_elapsed_time
  IS
  BEGIN
     DBMS_OUTPUT.
      put_line (
        'Elapsed ' || 'for ' || self.title || ' = '
        || TO_CHAR (

MOD (DBMS_UTILITY.get_time - start_time + POWER (2, 32)
                  , POWER (2, 32)));
  END;
END;

A player wrote to me asking why I included two calls to "POWER(2,32)" - which made me realize that I had never explained that fairly odd-looking code. The general algorithm for using DBMS_UTILITY.get_time is to call it once before you start your code (start time) and then again when your code finishes (end time). Subtract start time from end time and you are left with the number of hundredths of seconds it took to run the code. So what's with the POWER calls? The integer returned by DBMS_UTILITY.get_time gets bigger and bigger until it hits an OS-specific limit. Then it set to 0 and starts incrementing again. If you call DBMS_UTILITY.get_time to get your start time just before this "roll over" occurs, you will end up with a very large negative elapsed time (impossible!). So instead, I take the difference, add a really big number to it (bigger than the rollover point for operating system), then mod by that number. In this way, I am guaranteed a positive elapsed time. I like to use this story as an example of how even the most trivial-seeming formula will end up being more complex than you originally expected. Which is why you must always hide rules and formulas behind functions.

PL/SQL Challenge upgraded to 1.6

The PL/SQL Challenge has now been upgraded to 1.6. Key new features and enhancements include:

• Edit Profile page has been reorganized to give you access to more information and manage your account more easily. You can see any missed quizzes forgiven, as well as other adjustments. You can also view the results of all quizzes taken and request forgiveness for a "double viewing" of the quiz.
• Winners page now showcases the playoff winners and the rankings of everyone who played in the playoff.
• The Take the Quiz page now allows you to review Assumptions without leaving the page; it also shows the author of the quiz and the Oracle version applicable to the quiz.
• The site now offers more flexibility to offer special competitions and "breaking news" about such events on the website.
• Daily Rankings now available on the Rankings page! This has been requested for quite awhile. Players have also asked for much flexibility in analyzing ranking data. Be patient! 1.7 will offer big progress in this area.

Quick glimpse ahead to 1.7 (and beyond?):

• New ranking interface based on APEX4 interactive reports: you will be able to build your own reports, slice and dice ranking information however you want!
• Allow players to see all past quizzes and "export" questions and answers for your own future reference.
• Allow players to choose their own prizes when they win awards.

But mainly I will work hard at writing really interesting, unambiguous and mistake-free quizzes!

Cheers,
Steven Feuerstein
Founder, PL/SQL Challenge

Corrections for 9 September quiz complete (1375)

After much discussion on this blog and great feedback from players, I have decided to revamp this question to make it more clear. The original version was, in short, a mess. Everyone therefore gets the same score, 100% correct, for this quiz. Please note: the question text and choices now are different from when you took the quiz!

9 September Needs "Extreme Makeover" (1375)

Check out the post before this one and all the comments before reading further here. I have decided that the 9 September was a disaster of my own making. The main problem is that I decided to include packages and types in my definition of a PL/SQL block. Certainly they are program units in PL/SQL, but as the Oracle documentation says (and pointed out to me by Vitaliy Lyanchevskiy): "The basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can be nested inside one another." I should have stuck with this definition and then the choices would have been a bit more understandable (though my use of certain phrases still was problematic). So I am going to execute an "extreme makeover" on this quiz. I will change the question so that it is explicitly about the "basic units" described above. I will clean up the choice language to avoid the ambiguities with "may" and "never". And I will give everyone a 100% correct score for this quiz; I will also change everyone's time on this answer to 60 seconds so that you all end up with the same weighted score. And then I sincerely hope we can all move on. And I can creating so much confusion in the future. SF

Ambiguity in 9 September quiz requires score adjustment (1375)

This has been a tough week. In hindsight, I see the general problem: I had lots of "text only" quizzes. In other words, I presented you with sentences and asked you to evaluate their correctness. This automatically raises the bar in terms of clarity - and I did not reach that bar today. One of the choices was: "A trigger may not have an exception section." When I wrote this, I was thinking: "You are not allowed to have an exception section in a trigger." Which, I am sure many of you would agree, is obviously not true. Sadly, it was also quite easy and reasonable to interpret this statement as "A trigger might not have an exception, but also maybe it could have one." Which is just as obviously true. My deepest apologies. I will take the following action: 1. Change the text of this choice to "An exception section is not permitted in a trigger." There. That's lots clearer, isn't it? 2. Give all players credit for this choice. 3. Rescore and rerank. 4. Award Rene M with an O'Reilly ebook for reporting this ambiguity first. I just returned from travels over oceans so I may not get to this tonite, but it will be done before I choose winners for the week. There were some other comments about this quiz. I offer them below and look forward to your responses. a. "1. DECLARE BEGIN NULL;END; - Is this an existing execution section? There is a NULL but there is nothing thus there is no execution section. I think that both answers should be correct - this code either has some and has no execution section ;" My answer: yes, that is an execution section, it just doesn't do anything. I really don't think this is ambiguous. b. Regarding another choice that read "You never have to provide any declarations in a PL/SQL block.", Marcus wrote: "Sorry, I'm not a native speaker. To me it is not clear what the exact meaning is. Does it mean "it is never necessary to do so" or "it is not necessary if you don't need a variable"? This is only to show that even (to a native speaker) seemingly simple grammarcan be a problem. I don't want you to even consider a reranking, but did not want to let you know." My response: whew. Yes, it is hard for me to see the difficulty in interpreting this sentence. And it makes me feel every more strongly that I need to concentrate on offering quizzes that are driven by code and not text. Thanks for your patience! Steven

Typo in 8 September Quiz leads to rescoring (1374)

I kind of like mistakes in my quizzes that reveal subtelties in the PL/SQL language about which I was not aware. I really don't like mistakes in my quizzes that typos, which somehow escaped our code and text review process. Unforunately, the quiz on 8 September contained just such a typo. The question asked: Which of the following blocks work with Booleans and display "Truly false" (without the double quotes) after execution? And all the answers references the string "Truly False" (an upper case F). Which meant that all the choices were incorrect. Except that it was a typo and not my intention - and I believe most of you didn't notice and answered the quiz as though the strings matched. I am very sorry about this. Here's what we are going to do: I will fix the typo. Aanyone who submitted "incorrect" for all five choices will be credited with 100% correct answers - and I will change your answers so that it was as if you did choose the (now) correct choices. I will then recompute scores and re-rank. Congratulations to Marco Grimm for being the first to report this mistake. You win the O'Reilly Media ebook of your choice.

Mistaked reports in 7 September quiz on error handling (1373)

Players reported two problems with the 7 September quiz, which asked "Which of these choices correctly describe the behaviors of the SQLERRM and DBMS_UTILITY.FORMAT_ERROR_STACK functions?" 1. First, we scored the following choice as correct: "When executing SQL statements inside your PL/SQL program, Oracle may raise NO_DATA_FOUND, TOO_MANY_ROWS or DUP_VAL_ON_INDEX. If you handle any of these exceptions in the same block in which the exception was raised, then the string returned by DBMS_UTILITY.FORMAT_ERROR_STACK is the same as that returned by....SQLERRM();" In the answer, I provided a block of code that shows, simply by looking at the output on the screen, that this answer is correct. Well, shows how much I know about PL/SQL (not enough) and how important it is to not rely simply on "eyeballs" to verify results. Jaydeep Cheruku (the first to report this issue) was not content to merely peer at the screen and accept that those two strings were the same. He also checked the length of the strings and discovered that DBMS_UTILITY.FORMAT_ERROR_STACK is one byte longer: it has a carriage returned appended to the end of the error stack string. If only I had written "appears the same" instead of "is the same"! Ah well, that's what I very much enjoy about the PL/SQL Challenge: a chance to learn even more about the PL/SQL language. 2. In another choice (also scored as correct) stated "The SQLERRM function can be used to look up the error message associated with a particular error code, while DBMS_UTILITY.FORMAT_ERROR_STACK will only return information about the currently-raised exception, if any." Eigminas Dagys was the first to question this, by pointing out that if the string returned by the DBMS_UTILITY function contains a stack of errors, it contains information about the all the exceptions raised, not just the "currently-raised exception." Sigh...the pitfalls of language as a communication mechanism. I believe I could argue that this statement is correct; that the error stack returned by DBMS_UTILITY.FORMAT_ERROR_STACK is, in fact, all about the "currently-raised exception." t would be more accurate, however, to say "most-recently raised exception." So I will apply corrections to scoring and make changes to the question text to tighten up the meaning. So Jaydeep and Eigminas each win their choice of an ebook from O'Reilly Media. Thanks for your close, very close, reading of my quizzes and for helping me improve them. Thanks to all the other players who also responded to this quiz, raising the same issues.

PL/SQL Challenge Competition at Oracle Open World

[NOTE: I have updated this posting on 10 September to reflect changes in our plans for the PL/SQL Challenge competition at OOW. We will no longer use a special domain name - you will play at plsqlchallenge.com as usual. A brand-new posting on this blog will provide all current details.]

In the most recent newsletter, I included the following information about the upcoming PL/SQL Challenge competition at Oracle Open World:

As part of its sponsorship of the PL/SQL Challenge, OTN is also sponsoringa live PL/SQL Challenge competition during Oracle Open World. Instead of visiting the usual domain, you will take the quiz via oow.plsqlchallenge.com. When you submit your answer, you indicate if you are physically present at OOW. Then on Wednesday at 4:30 PM San Francisco time, we will gather at the OTN Lounge to give out lots and lots of prizes, some available to anyone who played anywhere in the world, some just for those who are physically present.

One player wrote to say: "One could read the text as if oow.plsqlchallenge.com is used instead of the

normal domain, although I don't think that is what you mean to say?"

In fact, that is precisely what I mean and here's a more complete explanation (we will also have lots more information available on the website as the date approaches):

From September 19 to September 22 (start and end times all in UTC), if you play the daily quiz, you will be eligible to win not only the usual PL/SQL Challenge prizes, but additional prizes that will be awarded after the quiz finishes on Wednesday, September 22nd.

In addition, if you are physically present at OOW, you may win special prizes reserved for those in attendance. When you submit your answers, you will be given the opportunity to check a box indicating that you are attending OOW and can pick up a prize in person.

I hope that clarifies matters.

Regards, Steven