04 October 2010

Q3 Championship Playoff Announcement

Now that September has passed us by, it is time for the 2010 Q3 Championship Playoff!

I am pleased to announce (and offer my congratulations to) the following 81 participants in this playoff (rules for qualification may be found on the Rules page, and are either through ranking, correctness level or wildcard selection):

João Barreto from Portugal (Ranking)
Kim Berg Hansen from Denmark (Ranking)
Riccardo Buttice' from Italy (Ranking)
Amir Cohen from Israel (Ranking)
Oleg  Gorskin from Russia (Ranking)
John Hall from United States (Ranking)
Niels Hecker from Germany (Ranking)
Jeff Kemp from Australia (Ranking)
Dennis Klemme from Antigua and Barbuda (Ranking)
Elic from Belarus (Ranking)
Johan Martensson from Sweden (Ranking)
Eurico Matos from Portugal (Ranking)
Richard Meyer from United States (Ranking)
Pavel Mitrofanov from Russia (Ranking)
KartikPatel from India (Ranking)
Yuriy Pedan from Ukraine (Ranking)
Sergey Porokh from Australia (Ranking)
Jeroen Rutte from Netherlands (Ranking)
Frank Schrader from Germany (Ranking)
Filipe Silva from Portugal (Ranking)
Adriano Teixeira from Portugal (Ranking)
Marc Thompson from Australia (Ranking)
Nopparat Vanichrudee from Thailand (Ranking)
Scott Wesley from Australia (Ranking)
Pavel Zeman from Czech Republic (Ranking)
Hans Henrik Andersen from Denmark (Correctness)
Christopher Beck from United States (Correctness)
Henry Cortez Wu from Singapore (Correctness)
Jen Croy from United States (Correctness)
Michal Cvan from Slovakia (Correctness)
Eigminas Dagys from Lithuania (Correctness)
Soumyakanta Das from India (Correctness)
Andrew Fenton from United Kingdom (Correctness)
Randy Gettman from United States (Correctness)
Davide Gislon from Italy (Correctness)
Radoslav Golian from Slovakia (Correctness)
emha from Slovakia (Correctness)
Dan Kiser from United States (Correctness)
Dalibor Kovac from Croatia (Correctness)
Robert Marz from Germany (Correctness)
Michael Meyers from United Kingdom (Correctness)
glenm from Australia (Correctness)
Filip Nikšic from Croatia (Correctness)
Alen Oblak from Slovenia (Correctness)
V Vandana Patel from India (Correctness)
Alexey Pirogov from Russia (Correctness)
Alexander Polivany from Ukraine (Correctness)
Chris Roderick from Switzerland (Correctness)
John Rowbottom from United Kingdom (Correctness)
Fabio Sangalli from Italy (Correctness)
Javid Sch from Azerbaijan (Correctness)
Jennifer Schiltz from United States (Correctness)
Tony Scholefield from Australia (Correctness)
Tim Scott from United Kingdom (Correctness)
John Seaman from New Zealand (Correctness)
Paul Sharples from United Kingdom (Correctness)
Sean Stuber from United States (Correctness)
Pietro Toniolo from Italy (Correctness)
Hrvoje Torbašinovic from Croatia (Correctness)
sudarshan Veer from India (Correctness)
Tony Winn from Australia (Correctness)
Kiril Yershov from Israel (Correctness)
Markus Zuser from Austria (Correctness)
jaydeep cheruku from India (Correctness)
pinkal soni from India (Correctness)
james su from Canada (Correctness)
Toine van Beckhoven from Netherlands (Correctness)
Rob van Wijk from Netherlands (Correctness)
Piet van Zon from Belgium (Correctness)
Gunjan from India (Correctness)
al0 from Germany (Correctness)
Morten Braten from Norway (Wildcard)
Justin Cave from United States (Wildcard)
Dan from United Kingdom (Wildcard)
Xavier Descamps from French Republic (Wildcard)
Justis Durkee from United States (Wildcard)
dannyg64 from United States (Wildcard)
Michael Haynes from United States (Wildcard)
Gary Myers from Australia (Wildcard)
William Robertson from United Kingdom (Wildcard)
Peter Schmidt from Germany (Wildcard)

The distribution of players by country is as follows:

United States           12                                     
Australia               8                                      
India                   7                                      
United Kingdom          7                                      
Germany                 5                                      
Italy                   4                                      
Portugal                4                                      
Russia                  3                                      
Croatia                 3                                      
Slovakia                3                                      
Netherlands             3                                      
Denmark                 2                                      
Ukraine                 2                                      
Israel                  2                                      
French Republic         1                                      
Norway                  1                                      
Belgium                 1                                      
Thailand                1                                      
Sweden                  1                                      
Switzerland             1                                      
New Zealand             1                                      
Czech Republic          1                                      
Singapore               1                                      
Austria                 1                                      
Belarus                 1                                      
Azerbaijan              1                                      
Lithuania               1                                      
Canada                  1                                      
Slovenia                1                                      
Antigua and Barbuda     1                                      

We had 48 participants in the first playoff; the number is much larger this time due to the algorithm for correctness participation. The #10 ranked player had a correctness level of just over 91%, which led to lots of other players being invited to the playoff. [Advance notice: I plan to tweak this algorithm for future playoffs to limit the number of participants from the correctness category. They should not overwhelm those who qualified via ranking.]

I plan to hold the playoff competition on Tuesday, 12 October 2010, at 13:00 UTC, which translates to:

6 AM in California
8 AM in Chicago
2 PM London
3 PM Most of Europe
6:30 PM in New Delhi
9 PM in Singapore
Midnight in Melbourne

I realize that this is not terribly convenient for everyone (OK, that is an understatement for West Coast USA and Western Australia). Finding a good time for everyone is fairly impossible with a completely global competition like the PL/SQL Challenge - and it's not nearly as painful for Australians as the first playoff (which was held two hours later).

All participants will receive an invitation by email, and the opportunity to inform me if they cannot play. If more than 20% of participants cannot play at this time, I will find another date and time for the playoff.

Warm regards,
Steven Feuerstein

02 October 2010

Many thanks to my reviewers!

As we finish up another quarter and roughly 60 more quizzes at the PL/SQL Challenge, I would like to take a moment and some bits and bytes to recognize my quiz reviewers.

Ken Holmslykke, who was a top-ranked PL/SQL Challenge player, volunteered to stop taking the daily quizzes (and any hope of winning all those cool prizes) so that he could take on the task of checking each of my quizzes, which are often quite a mess before they make it your screen.

Darryl Hurley, who has over the years supported my book-writing efforts with both content (the chapter on triggers in Oracle PL/SQL Programming) and feedback, has also been instrumental in, well, saving me from myself.

While I still occasionally make mistakes that "sneak by" these two fine Oracle technologists, there is no doubt in my mind (and I have the emails to prove it) that they have saved me from many more mistakes, typos and downright misunderstandings than you would have thought possible.

Ken and Darryl, you have my unending gratitude for the commitment of your valuable time to the PL/SQL Challenge!

Warmest regards,
Steven Feuerstein

01 October 2010

Next Steps for Q3 Quarterly Championship

Today is the first day of the 4th quarter of 2010. This means that yesterday was the end of the third quarter, and that means that in roughly two weeks, we will hold the second quarterly championship playoff!

Over the weekend, I will be doing the following:

1. Apply a "too fast answer" adjustment for the last two weeks.

2. Generally review the quality of quiz data for the past quarter and make sure no problems exist (many players have noted, for example, that their ranking information indicates that they missed a quiz. Well, you didn't miss a quiz; instead, the quiz on 14 July was invalidated due to downtime on the site.).

3. Produce the list of qualifiers for the playoff (see Rules page for details).

I will then publish an announcement of players and tentative date/time for the playoff next week.

Warm regards,
Steven Feuerstein

Warning from player regarding 30 September answer (1444)

In the 30 September quiz on RETURNING, I scored as correct the choice that performed the update and then, separately, issued a query to get information back regarding the impact of the update. I wrote in the explanation: This solution issues the update and then in a separate SQL statement retrieves the part number of the row that was just modified - but only by reproducing the logic ("partname = UPPER (partname)") in the WHERE clause. This means that I have introduced repetition in my code, and also inefficiency (an extra context switch). This is logically equivalent to using the RETURNING clause, but definitely inferior to RETURNING. A player wrote to say: In your explanation of answer 3 for the September 30 quiz, you note that this solution is repetitious and less efficient than using the RETURNING clause. I suggest that it is even more important to note that this is only 'accidentally correct' - if the starting data had included other rows where partname = upper(partname) such as '2 GB RAM' then this code would not have worked. This is a good point, both specifically for this answer and more generally for the quizzes: in order to make the quiz questions and choices unambiguous, I construct very "small worlds" of data and code. I seek to demonstrate and exercise your knowledge on fairly specific features. This means that you should be able to trust the lesson of the quiz, but you should not adopt/follow the code samples as any sort of general pattern for a solution to your real-world problems. In the case of this choice in the quiz, I should be (and will update the answer so that I am) more explicit in drawing out the lesson here: If you use a SELECT after your DML statement to determine if the correct changes were made, you need to be very careful about how you specify the WHERE clause of your query to be sure that you identify the same rows that were (possibly) changed.

29 September 2010

Missing RETURN in 28 September quiz - but no impact on correctness! (1442)

Several players noticed that the function I included in the 28 September quiz was missing a RETURN statement:
CREATE OR REPLACE FUNCTION partnum_from_name (
  partname_in IN plch_parts.partname%TYPE)
  RETURN plch_parts.partnum%TYPE
IS
  l_return   plch_parts.partnum%TYPE;
BEGIN
  SELECT p.partnum
    INTO l_return
    FROM plch_parts p
   WHERE p.partname = partnum_from_name.partname_in;
END partnum_from_name;
Yes, missing a RETURN statement, no doubt about it. This bothered several players, and with good reason. If and when this function was executed it would (if one row was found for this part name) raise the following error:
ORA-06503: PL/SQL: Function returned without value
So what's the problem? Here's what one player wrote: "The function defined in the quiz on 28-SEP-2010 is invalid because it does not have a RETURN clause." Well, actually, that is not so. The function is valid, compiles without error. So in the context of this quiz, the lack of a RETURN statement had no impact whatsoever on the correctness of the choices. The function was compiled, and since it contained a select from the plch_parts table, the dependency view would reveal that information - no matter the run-time problems that might be encountered when the function was executed. Still, it was not my intention to leave out the RETURN statement, so I will add it into the question text to avoid this distraction in the future. In addition, and somewhat to my surprise, several players thought that I had made a mistake when writing a SELECT statement as follows:
SELECT COUNT (*)
 FROM all_dependencies
WHERE     referenced_name = 'PLCH_PARTS'
      AND referenced_owner = USER
      AND referenced_type = 'TABLE' 
Specially, they felt that the word "USER" should have been within single quoates, as in:
SELECT COUNT (*)
 FROM all_dependencies
WHERE     referenced_name = 'PLCH_PARTS'
      AND referenced_owner = 'USER'
      AND referenced_type = 'TABLE' 
But that would only work if the name of the schema that owned PLCH_PARTS was "USER". USER, without single quotes, is a built-in function that returns the name of the currently connected schema. So all in all, nothing to change in the quiz scoring. Thanks for writing with your comments! Cheers, SF

27 September 2010

24 September quiz prompts warnings from players (1403)

The 24 September quiz tested your awareness of string-indexed collections, and how you can use these to effectively change the sort order of the data in one's collection. Two players wrote regarding this quiz as follows: Daniel: "The solutions only work in very specific scenarios. If the text value was to be repeated, ie: 2 Michael Jordan entries, then only one of them would be displayed. So I don't think that solutions provided, other than the Insert/Select are very safe when dealing with large data sets." Patrick: "I would suggest to add a big warning to one of the answers of the question of 24-Sep. I know that the answer creating a table on the fly, inserting the data and selecting from there is syntactically correct, but I think it should almost never be used that way in a real program. The simple reason for that is that it's not multi user/session ready. If another use is currently using that function, a second user would get an error message when he tries to create the same table on the fly. Especially in web applications, most sessions are sharing the same database runtime users. I wanted to point that out, because many novice developers might take your examples as "facts" how a more complicated sort could be solved, but I think that answer needs a big warning that this is just a theoretical solution and should NOT be used in real world applications." Both Daniel and Patrick make good points (I have changed one of the answers to include Patrick's warning, in fact) and also make me realize how important it is for players to recognize that these quizzes will almost always be focused on particular features of the PL/SQL language. These features are inevitably presented outside the context of "real world" application development, in which many factors come into play. I will try to be better about including warnings in answers, when a theoretically correct solution could raise practical, real-world issues - but I also rely on and trust you, our devoted players, to let me know whenever you have a concern. Also, I am looking into how I can also present best practice oriented topics in the daily quiz in an unamibguous fashion ("best practices" inherently have a good does of subjectivity associated with them). If you have any thoughts about how I might do that, or specific ideas for quizzes, don't hesitate to let me know. I also plan to reorganize the blog so that it can be directly linked to the quizzes, allowing discussion of features and application of those features to be more visible to everyone playing the quiz. Cheers, Steven

26 September 2010

ODTUG Winners for Oracle Develop week quizzes

I am pleased to announce the following winners of ODTUG-sponsored prizes for playing the quiz last week:

Complimentary registration to the ODTUG Kaleidoscope 2011 conference, a US$1775 value: Gunjan

Complimentary registration in your choice of the Oracle PL/SQL Programming/APEXposed conferences, a US$600 value: V Vandana Patel  and jaydeep cheruku

Complimentary membership in the ODTUG organization, a US$175 value: Gracelin Jeyananth

Congratulations to all winners! ODTUG will be following up with you provide instructions for redeeming your prizes.

Steven Feuerstein