New rankings now in place

You will notice big changes in the rankings between last week and this week.

Over the weekend, we put into place a modification to the algorithm by which we score and rank players.

Before going into the details of the change, please note that on the Rules page we say:

"We reserve the right to change the scoring and ranking algorithms at any time; such changes may be applied retroactively in the current quarter. We guarantee, however, that the algorithm will always be applied equally to all players."

We don't make such changes lightly, but in this case it was required - and the result is a much more accurate reflection of the way players compete on the PL/SQL Challenge.

Some background: in the process of analyzing quiz answer data, we noticed that many players were visiting the quiz page more than once in a day. That is, you log in, press Take the Quiz button and then "I'm Ready! Start the Quiz Now" button, at which point you see the quiz for that day. But these players were not submitting their answer at this time. Instead, they were coming back later in the day to go through the same steps and then press Submit Answer.

Of course, in many cases, this is happening because a player might lose a connection or have to "get back to work!" (so sayeth the unenlightened manager). But in several other cases, we have players who routinely "double visit" the quiz page - which can offer a significant advantage, and might even be considered a form of cheating.

Putting aside the issue of cheating for a moment, we decided that we can and should adjust our algorithm to take this behavior into account by changing the way we calculate the number of seconds used to answer the question. Previously, the number of seconds was computed as the amount of time between pressing the "Start the Quiz Now" button and the "Submit Answer" button, in that same session (this will be referred to as TQ below).

From now on, the number of seconds will be computed as follows:

FV = Time of first visit to quiz page
LV = Time of last visit to quiz page

Number of seconds = (LV - FV) + TQ

The downside of this change is that if you visit the quiz page and then lose your connection and cannot return for a long time (or some other similarly innocent scenario), you will receive a greatly-reduced score. If this happens you can contact us via the Feedback button and request "forgiveness" for that gap in time. We will likely make the adjustment if it happens only very rarely.

The upside is that the score now much more accurately reflects the time that a player takes to figure out the correct answer to a quiz.

We hope you agree with these changes; if you have any questions of concerns, please reply to this blog or provide feedback through the PL/SQL Challenge site.

Advance notice of upcoming change in scoring/ranking

We are just coming up to the end of the first quarter of the PL/SQL Challenge. Now that we have gathered lots of data from thousands of quiz answers over three months, we have started doing intensive analysis of that data, looking for various patterns.

One consequence of our analysis is that we are changing the algorithm we use in scoring and thus ranking. We will publish a full explanation on Monday, but we wanted to give you advance notice of three things:

1. The #1 ranked player for the week as of Friday may not be the #1 ranked player for this week come Monday, so that prize will not be awarded until early next week.

2. You can expect to see major changes in the ranking on Monday, so don't be shocked. Or, rather, feel free to be shocked, but at least not surprised.

3. Looking further ahead, we plan to hold the championship playoff in the middle of July, not immediately after the end of June. We need time to, quite honestly, finish implementing the playoff process and also to identify all participants in the playoff.

Thanks so much for your patience as smooth out the functioning and accuracy of the PL/SQL Challenge. We know that your rankings on the website are important to you, and we are doing everything we can to ensure that your ranking accurately reflects the effort you put into answering those quizzes!

Warm regards,
Steven Feuerstein and Finn Ellebaek Nielsen
The PL/SQL Challenge
http://www.plsqlchallenge.com

Questions raised about 21 June Quiz - what do you think?(409)

The question was: "I declare a nested table or varray of numbers. I then want to assign the number 10 to index value 1, as in:

my_collection (1) := 10;

"Which of the statements correctly describe a step I must take after declaring my collection and before I can successfully execute the above statement?" And we at PL/SQL Challenge claimed that the only correct answer was: "You must initialize the collection by assigning it a value returned by a call to a function provided by Oracle that has the same name as the type from which the collection was declared." Two players wrote to question this viewpoint as follows: 1. "Hi, I disagree with your answer to yesterdays quiz. You've checked the "You must initialize...." answer as correct. I think it's not a complete answer, because it lacks the the use of "extend". I can bypass using extend by using a dummy value in the constructor. But that is IMO such a bad practice that I don't consider it a correct way to do it." 2. "Do we must initialize nested table? Yes. Do we must do it by calling default constructor? No. We can call another function returning initialized collection.We can construct a collection using bulk collect. So we indeed do not must call default constructor.Although it's a typical way we have more choices.That is why i consider your answer literally incorrect." Here is our answer to these objections: 1. We never said that calling a constructor function was the only thing you had to do before you could execute the assignment. It is, certainly, "a step I must take." The player is right; a call to the EXTEND will almost always be needed as well. But that doesn't make the choice incorrect. 2. The player is right. You could call your own function to initialize the collection; you could also avoid the need to call a constructor by populating the collection with a call to BULK COLLECT. Having said that, please refer back to this assumption which appears before you take each quiz: "The session and the environment in which the quiz code executes has enabled output from DBMS_OUTPUT, and can reference only those datatypes, programs and database objects defined in the context of the quiz or are available in a default installation of the Oracle instance." This means that unless we explicitly say that we have created in the context of this question another such function, it does not exist and you cannot assume its use in the answer. As far as BULK COLLECT goes, in general, that will allow you to avoid the need to call a constructor, but in this particular case (performing a scalar assignment), that does not apply. So we believe that while these are interesting and informed questions to raise about the 21 June quiz, all your answers were scored correctly. What do you think?

PL/SQL Challenge server was down from 15:45 to 16:30 UTC

We were notified that the server hosting the PL/SQL Challenge was experiencing technical difficulties, starting at 15:45. The issues were resolved and the site back up at 16:30 UTC.

If the PL/SQL Challenge is unavailable for at least four hours, then the results of the quiz for this day will not be used to compute rankings.

Since this outage was shorter than that, today's quiz results will be used in ranking.

Our apologies for any inconvenience,
Steven and Finn

Use REGEXP_SUBSTR to get line number on which error was raised?(408)

In the 17 June 2010 quiz, we asked: "What information about a PL/SQL exception can be obtained inside an exception section of a PL/SQL block by calling a built-in or packaged function provided by Oracle?" One of the choices was "The line number on which the exception was raised". This choice was correct, because you can get this information with a call to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. One of our players, however, offered the following as an alternative: Hi, you'll most probably regard option 1 of today's quiz "the line number on which the exception was raised" as wrong [no, we didn't!]. What I thought when clicking this option as "correct", is described by following example:

DECLARE
  i   INTEGER;
BEGIN
  EXECUTE IMMEDIATE ('BEGIN :i := i_dont_exist; END;') USING OUT i;
EXCEPTION
  WHEN OTHERS
  THEN
     DBMS_OUTPUT.
      put_line (
        '*****error found in ' || REGEXP_SUBSTR (SQLERRM, 'line [0-9]*'));
     DBMS_OUTPUT.put_line ('HERE COMES THE ERROR MESSAGE');
     DBMS_OUTPUT.put_line (SQLERRM);
END;

What do you think? Is this a good alternative to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE?

A reminder regarding cheating and the PL/SQL Challenge

We are coming up on the end of the first quarter of playing the PL/SQL Challenge. In early July, we will hold the first championship playoff, with a first prize of 1000 USD. We feel, therefore, that it is a good time to remind all players about the rules regarding cheating.

To read all the PL/SQL Challenge rules, click here.

The PL/SQL Challenge offers cash prizes and will become a means by which programmers can establish or improve their reputations as PL/SQL developers. It is serious stuff, in other words. And that means that it is possible that  someone will try to cheat or "game the system" to improve their chances of winning a prize and elevated status. We have given lots of thought to the ways that people could cheat. We have taken several measures to minimize the chance of successful cheating. And we have decided on the following consequences.

If you violate any of the rules of the PL/SQL Challenge or cheat in the process of submitting your answer, then you will lose all your points for the current month (any previously submitted and any others submitted for the rest of the month). If this occurs a second time, your account will be closed and all accumulated points lost.

Here are what we consider to be examples of cheating (this list is by no means exhaustive; if you find another way to cheat and it is not on this list, you will still be penalized when you are discovered):

• Creating multiple accounts: you may only create one account for yourself in the Challenge.
• You take the quiz in one account, taking your time to look up the answer, study the code, etc. Once you are sure of the answer, you log into your "real" account and submit your answer quickly, to game the timing algorithm.
• You receive the quiz from someone else, study and determine the correct answer, and submit your answer quickly, to game the timing algorithm.
• You initiate or participate in any sort of discussion forum/thread/SMS on the quiz during the day in which the quiz is active. Feel free to discuss the question and answers after once that quiz is "history." While people can still answer it, however, please refrain from publishing information or discussion about it.
• You use any kind of a "bot" to submit your answer or answers.
• You start the quiz, study and research until you are ready to answer the question, then you refresh the webpage in hopes of restarting the clock. It won't help you, but it's still cheating.
  

14 June Quiz: Why does VALUE_ERROR exception go unhandled?(366)

In this quiz, we present a block of code that includes this line:

value_error EXCEPTION;

In other words, we declare an exception with the same name as an exception defined in the STANDARD package of Oracle (and thus can be referenced without a package qualifier name, as in STANDARD.VALUE_ERROR). By doing so, if Oracle raises its VALUE_ERROR exception, a WHEN clause like

WHEN VALUE_ERROR

will not trap the exception. Tricky, yes....and at least one player had some comments on it, so take a look...