Input needed for the Past Quizzes page

Dear players,

I plan for the 1.7 release of PL/SQL Challenge (tentatively scheduled for 23 October) to improve the quantity and quality of information shown on the Past Quizzes page.

Once this page is re-designed, I then plan to send out the same information in the "yesterday's quiz" email.

So if you have any thoughts about how you'd like to see this page and/or the daily email changed, please post your ideas here (or, if you do not have a Blogger/Gmail account, send them directly to steven@stevenfeuerstein.com).

Many thanks,
Steven Feuerstein

When is %ROWTYPE used to define the record structure? (1461)

The 4 October quiz recorded as correct the following statement: "You can declare a record based on a table, so that every time you compile the block in which the record is defined, the PL/SQL compiler defines a field in the record for each column in the table, with matching name and datatype." One player wrote to me with the following comment (I bolded the key words):

Steven, in the explanation for this choice you write "You can declare a record based on a table, so that every time you compile the block in which the record is defined, the PL/SQL compiler defines a field in the record for each column in the table, with matching name and datatype." You show an anonymous block illustrated this, i don't know if this is "almost" true, coz i can create a procedure, compiled, modified the table, and i don't need to compiled again to take effect the %ROWTYPE. i think is more like "that every time you -execute- the block in which the record is defined, the PL/SQL compiler defines a field in the record for each column in the table, with matching name and datatype."

This is definitely not true. The "lookup" of the structure of the table, view or cursor, to then use to define the fields of a record type in PL/SQL definitely happens at compile time. When you are executing an anonymous block, the PL/SQL compiler must first compile that block before it is executed. That's when the lookup occurs. If you have defined a stored program unit, like a procedure with a %ROWTYPE declaration, then whenever that program unit is re-compiled, Oracle again "looks up" the structure of table, view or cursor. Some of the confusion might arise from the fact that you do not have to explicitly recompile your program unit after you change a table referenced in %ROWTYPE. Oracle automatically marks that program unit as invalid and will also automatically attempt to recompile it the next time you try to use that program unit. But there is no doubt that this is compile-time and not run-time behavior. There is no way that you or Oracle would want to slow down production performance by looking up the structure of tables and views every time the program unit is executed! Cheers, SF

Oh, no - Ambiguity strikes again in the 4 October quiz (1461)

The English language (or, rather, my use of it) strikes again! The 4 October quiz was one of those non-code, all-words quiz and it burned me/us again. The quiz question was: "Which of the following statements about PL/SQL records are correct?" Players complained about three (yes, three) of the choices. But I don't agree with all complaints. :-) Let's take a closer look.... 1. "A record contains an audio or video recording for storage in the Oracle database." As one player wrote: "I chose to read it as: 'A record may contain an audio or video recording...' Obviously, it is a column within the record that contains the multimedia experience, which means the record also does.' My response to this interpretation is: I disagree. That is, I suppose I understand why you might read the choice this way, but I do not believe it is a reasonable interpretation - I don't think there is ambiguity here. The question makes it clear that I am asking you to identify correct statements about records in general. It is true that a record might contain a "recording"/BLOB field. But it doesn't have to and the choice clearly (or at least, clearly enough, to my mind). This statement is not true for "PL/SQL records" as the question asks. Furthermore, it should be well understood that a PL/SQL record is not necessarily stored in the Oracle database (as a row in a table). So this statement was incorrect on several counts. I will not change scoring for this choice. Another player wrote about this choice: "I would have preferred this option to say 'A record ALWAYS contains a video/audio recording...' to remove the ambiguity." Yes, that would certainly be more explicit, but I also feel that it would "give too much away." 2. 'While it is possible to assign NULL to a record, it is not possible to check to see if every field in the record is null using the "IS NULL" operator.' Sigh. Yes, here we have an ambiguity that I cannot explain away. I wanted you to know or learn that you cannot use the IS NULL operator with the record variable itself. My decision to include "every field in the record," however, clearly invites you to interpret this choice as correct, in that you can check the contents of each field using IS NULL (or, if a field is a record, you could in turn use IS NULL on each of its fields, etc.). I should have made it clear that I wanted the IS NULL to be applied to the record. I did not, and so I will give all players credit for this choice, change the text to make it more explicit, and change all of your answers to indicate "correct" for this choice. 3. "You cannot check for equality between two records using the "=" operator. " One player suggested that this choice has the same ambiguity as #2. That is, you can use "=" to compare the values of individual fields of the record. I disagree with this interpretation. I believe it is quite clear that this choice asks you to decide if you can use the "=" operator with the records themselves, not their fields. I will not changes scores on this choice. So, sorry about that and on to the next quiz! Fortunately, it has code in it. :-) Cheers, Steven Feuerstein

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

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

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.