17 July 2010

Regarding requests to make all past quizzes available

Several players over the past few months have asked us to "open up" the Past Quizzes page to make it possible for players to see all past quizzes. Here is an example of a typical request:

"I would like to see a search option added so we can search all past quizzes by topic as a reference. For instance, search for "bulk collect" and we would find the quiz results from June 16..."

That sounds like a great idea. A really great idea. Imagine having a searchable database of hundreds, and eventually thousands, of Q&As, backed up by links to documentation, explanations of the reasons for the answers, and perhaps even videos demonstrating a technique or feature of the language.

It's such a great idea, that I am in active planning stages to build a whole website that will deliver my expertise in the PL/SQL language through various means: a testing database (not just quizzes, but user-defined and curriculum-based tests on given areas), an extensive library of video trainings linked to specific topics or features, a repository of reusable code, and more.

I hope that you read the above paragraph and think to yourself: "Wow, that sounds great. I would love to have access to all that information and expertise." And I further hope that you then think something like: "Heck, I would even pay (or get my company to pay) to have access to this expertise."

I say that because I plan to charge a fee for access to this broad, deep content. I am definitely not comfortable with giving it all away for free (and if I make all past quizzes available all the time, then the effectiveness of these Q&As as a testing database is greatly diminished).

In fact, my plan is to largely stop traveling (it's been great fun and wonderful to meet so many people, but being away so much is not the best thing for me or my family) and instead concentrate on delivering my expertise online.

What do you think of my idea? Would you pay for this content or do you think your company would? I would very much like to hear your thoughts.

This plan also explains why, at least for the foreseeable future, I will keep the restrictions in place on the number of past quizzes you can see through the PL/SQL Challenge site.

Reminder emails sent erroneously on the weekend

As many of you will have discovered, reminder emails were sent out on Saturday (and I assume they will be coming on Sunday, as well). This is a bug and it will be corrected by next weekend. My apologies for these unnecessary emails.

Steven Feuerstein

Questions raised about 16 July quiz and primary key (503)

In the 16 July quiz, we wrote: The employees table (with columns named employee_id and salary, both of type NUMBER) is defined in the same schema as this function:
FUNCTION my_salary (employee_id_in IN PLS_INTEGER)
  l_salary   NUMBER;
  SELECT e.salary
    INTO l_salary
    FROM employees e
   WHERE e.employee_id = my_salary.employee_id_in;

  RETURN l_salary;
END my_salary;
Assume that the database instance is working properly (it is not in  process of shutdown, out of storage or memory, etc.). The only variation in behavior of the  function can be caused by the value of the employee_id_in argument. Which of the following exceptions could be propagated  unhandled out of this function?

A number of players raised a concern regarding the primary key on the employees table. Namely, if the employee_id_in column is the primary key of the table (and it is enabled), then TOO_MANY_ROWS would not be raised out of the function. Yet that choice was scored as a correct answer.

One player wrote: "It [lack of mention of the primary key] might confuse the players who know that Emplyee_ID is a PK in the SCOTT  schema."

Another wrote: "Is there a primary key or unique index on employees.employee_id? You must  provide enough information for challengers to answer the question."

And another: "We can assume that employe_id column is primary key in this case rite, since it  is not specified?"

Finally, on a different topic, a player wrote: " In the question you asked there was no CREATE OR REPLACE in the Function."

Here is my response to these comments:

1. The assumptions for taking the quiz include this: 

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.

Given this, you clearly should not assume that there is any primary or foreign key on this table, since I do not specify it in the question content. TOO_MANY_ROWS was a correct choice for this quiz.

Having said that, I will try to avoid in the future referencing tables for which a user might have a preconceived notion. That should minimize the chance of confusion.

2. Regarding the lack of CREATE OR REPLACE. I assume the player is implying that therefore the function is not compiled into the database and so the all answers will be wrong. I disagree. I stated that the function "is defined in the schema," so clearly it was compiled. "CREATE OR REPLACE" is not a part of the function definition. It is part of the DDL statement that creates the function.

16 July 2010

Wrong Quiz Taken Percent Displayed

A player noted that even though he has played every quiz this quarter, his quiz percentage is displaying as 90.1%, instead of 100%.

This is caused by the voiding of answers for the 14 July quiz.

I should remove the quiz entirely and that will clean up the % computation, but that turns out to take a bit more refactoring than I'd expected. I hope to have this fixed next week.

In the meantime, rest assured that the impact of this discrepancy applies to all players equally and does not affect your score or rankings.

15 July 2010

First PL/SQL Challenge Championship completed

The first PL/SQL Challenge Championship in the history of the entire world started twenty minutes and finished without any problems.

Thirty-two (32) of 48 eligible players logged in and competed. Please note that we stated that we would change the date/time of the playoff if at least 20% of players notified us in advance that they could not play. Only four players did so, so even though 16 players did not participate, the results are valid.

We will make the rankings and past quizzes page available tomorrow for all participants in the playoffs. This will give them the opportunity to review the results and contact us with any questions or concerns. Once we are certain there are no objections, we will publish the winners.

14 July 2010

Issue raised by player regarding 12 July quiz(505)

I received an email with the following statement: "Hello Steven and PLSQL challenge team, I reckon that the 3rd option in a recent quiz (12 Jul 2010) was incorrect, because "Any program unit" (2nd requirement in a question) may include client PL/SQL calls, like from Oracle Forms/reports where you cannot use default values for actual parameters." And here is my response: in our assumptions for the quizzes, we state: "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." Therefore, while what you say may be true, it does not apply to this or any other PL/SQL Challenge quiz, unless we explicitly introduce the idea of running client-side PL/SQL in that quiz. Please let me know what you think.

The results for 14 July quiz will be removed(501)

After some careful thought, we have decided that at the end of the day (UTC time), all answers submitted today will be archived. Then the entire quiz for today and its results will be removed. I really didn't want to do this; I know that many of you will be disappointed to not be able to see how you did. But to be quite honest, we don't have the logic in place to simply ignore a day and be sure that there will be no adverse impact on rankings. And we cannot build that in time to install safely in our production environment. So go ahead and play the quiz, but be aware that you will not be able to see how you did.

PL/SQL Challenge "Take the Quiz" is down: 14 July(501)

Our "Take the Quiz" page is not working. An Oracle is being raised and unfortunately it doesn't look like I can get it fixed until my UK-based developers return from dreamland and check their email. The PL/SQL Challenge policy is to void the quiz if it is unavailable for at least 4 hours. That is likely to be the case. My apologies to all players for this bug and the unavailability of the quiz. It is a big disappointment to me, but I suppose we shall all survive, somehow. After all, there was no daily PL/SQL quiz for many years and we all managed. :-)

13 July 2010

Past quizzes rendered as images - players don't like this

In the process of moving to rendered images for the quizzes, we also changed the Past Quizzes page to show images, making it impossible for players to copy the code in the questions. A number of players immediately wrote to complain about this.

We will change the Past Quizzes to show text and not images as soon as possible, but it may not happen till after the playoffs.

11 July 2010

Announcement of participants in the Q2 2010 Championship Playoff

We have successfully implemented the 1.4 release of PL/SQL Challenge. This means that we can now move forward with the historic, first championship playoff of the PL/SQL Challenge.

The playoff will be held on Thursday, 15 July 2010, at 15:00 UTC (10 AM in Chicago; 20:30 or 8:30 PM in New Delhi). We realize that this time may cause difficulties for some players. We apologize in advance for that, but everyone must take the playoff at the same time.

The following 48 players (listed in alphabetical order by last name or display name) are eligible to participate in the playoff. Congratulations to each of you.

You will find to the right of their names the basis on which they were selected for the playoff. Check the Rules page for more details on these three categories (Ranking, Correctness, Wildcard) and how the players were selected.

Each of these players will receive an email with more details on the playoff process. We will assume that if we do not hear back from you that you can participate. If you cannot participate at this time (and by that I mean that you really cannot physically be at a computer, not that it would be inconvenient for you), reply immediately. If more than 20% of the players (10 or more) cannot participate, we will change the time, but I very much hope that will not be necessary.

And now....the playoff participants:

Noor Ahmed (Wildcard)
Timur Akhmadeev (Ranking)
Guewen Baconnier (Ranking)
Janis Baiza (Correctness)
Rokas Baltuskonis (Correctness)
Fabio Cotta (Wildcard)
Michal Cvan (Ranking)
Rich Dellheim (Wildcard)
Jeyhun Gasimov (Wildcard)
Radoslav Golian (Correctness)
Muhammad Abdul Halim (Ranking)
John Hall (Ranking)
Niels Hecker (Correctness)
heho (Correctness)
Ken  Holmslykke (Ranking)
Jeff Kemp (Ranking)
Gerben Kroese (Ranking)
Eurico Matos (Ranking)
Marcus Matzberger (Correctness)
Oren Nakdimon (Correctness)
Cem Ozer (Wildcard)
Mike Pargeter (Ranking)
Sailaja Pasupuleti (Ranking)
Jens Petersen (Ranking)
Alexander Polivany (Correctness)
Michal Pravda (Ranking)
Marcio Reis (Wildcard)
Christian Rokitta (Correctness)
Fabio Sangalli (Ranking)
Javid Sch (Correctness)
Anton Scheffer (Ranking)
Filipe Silva (Ranking)
Muhammad Sohail (Wildcard)
Nick Strange (Correctness)
Adriano Teixeira (Correctness)
Roger Troller (Ranking)
Nopparat Vanichrudee (Ranking)
Henrikas Zukovskis (Ranking)
rahim ahmedov (Wildcard)
yves colin (Ranking)
sibanjan das (Wildcard)
shwetamber kaushik (Ranking)
kristine seco (Wildcard)
Toine van Beckhoven (Ranking)
Edwin van Meerendonk (Ranking)
Rob van Wijk (Ranking)
peter van der neut (Ranking)
Latha  (Correctness)

Questions raised by players regarding 8 July quiz (LOG ERRORS and SAVE EXCEPTIONS)(464)

The quiz on 8 July asked this question: "You can use both LOG ERRORS and SAVE EXCEPTIONS to continue past errors that occur when executing DML statements (inserts, updates, deletes and merges). Which of the following statements describe what happens when you use both in a single FORALL statement (assume that DBMS_ERRLOG.CREATE_ERROR_LOG was used to create the error log table as required)?" We scored as correct the following choice: "If, within a FORALL statement, you include (in a syntactically valid fashion) both SAVE EXCEPTIONS and LOG ERRORS with the rejection limit set to UNLIMITED, then the SQL%BULK_EXCEPTIONS pseudo-collection will always be empty after executing that statement." Two players expressed a concern about this choice. I will invite them to post their comments in reply to this blog. Then I will reply to them! SF