Was 22 September quiz on COALESCE missing critical code? (1401)

The 22 September quiz contained this text: "The procedure below accepts three arguments: the primary key to the building, the primary key to a room in that building, and the primary key of the item in that room...[skipping code]...The arguments passed to process_data always follow these rules:

• If the item key is not null, then the other two "higher" keys must also be not null.
• If the room key is not null, the building argument must not be null.
• The building key is never null.

"In other words, if you pass in values for the building key and the item key, then you must also pass in a non-NULL value for the room key." One player asked: "Shouldn't there be something that validates the input to ensure that all higher elements are not null such that if item is not null, make sure the room and building are not null before returning item as valid?...it does invalidate the quiz, but only from the standpoint that 1 of the requirements is not implemented. Namely, If the item key is not null, then the other two "higher" keys must also be not null. This is not checked for in the function." First, I do not think that this concern invalidates the quiz. I did not say that the program must implement these rules. I said that the "arguments passed to process_data always follow these rules." That is, I define as an assumption that the arguments do not need validation. They will "always follow these rules." Well, that might be fine for a quiz, but of course in the real world, making such an assumption would be a big mistake. Carl raises an excellent point here: any assumptions you make when you write your program should be validated as TRUE before you start running the code that relies on those assumptions. One very nice way to do this is to use assertion routines.You can download an assert package (in assert.pkg file) from my demo.zip. Here's what the procedure might look like with an assertion:

PROCEDURE process_data (
  building_in   IN plch_buildings.building_id%TYPE
, room_in       IN plch_rooms.room_id%TYPE DEFAULT NULL
, item_in       IN plch_items.item_id%TYPE DEFAULT NULL)
IS
  l_id   INTEGER;
BEGIN
  assert.
   is_true (
     CASE
        WHEN item_in IS NOT NULL
        THEN
           building_in IS NOT NULL AND room_in IS NOT NULL
        WHEN room_in IS NOT NULL
        THEN
           building_in IS NOT NULL
        ELSE
           building_in IS NOT NULL
     END
   , 'All arguments from highest to lowest key values must be NOT NULL.');
 
  l_id := COALESCE (item_in, room_in, building_in);
 
  DBMS_OUTPUT.put_line (l_id);
END process_data;

And when I run this program with invalid arguments, I see an error:

BEGIN
  process_data (building_in => 1, room_in => NULL, item_in => 2);
END;
/

ORA-20000: ASSERTION VIOLATION! All arguments from highest to lowest key values must be NOT NULL.

Thanks for pointing this out, Carl! SF

Announcement of Prize Winners at Oracle Develop / Oracle Open World

The three-day contest at Oracle OpenWorld and Oracle Develop on the PL/SQL Challenge has now ended!

We have analyzed the answers from everyone who played the daily quiz over the last three days and also checked the box indicating they were in San Francisco and could pick up their prizes.

Winners of the Oracle OpenWorld and Oracle Develop prize pool can, in fact, pick up their prizes by visiting the Mason Street Tent Thursday, September 23, anytime between 7:00 am - 4:30 pm to pick up your prizes at the Oracle Technology Network info desk. Steven Feuerstein will be present from noon to 1 PM to congratulate you, sign books, and join you in a celebratory photo - if you so desire. If you do not claim your prize Thursday, September 23 by 4:30 PM it will be forfeit. So much for the fine print. Let's get to the Big News!

Congratulations to everyone who is listed below (note: the names displayed are those chosen by the PL/SQL Challenge player). We hope that you will all continue to play the PL/SQL Challenge each day and tune up your PL/SQL skills and knowledge even further.

Warm regards,
Steven Feuerstein

First Place

Tim Lindemulder placed first with a score of 1171 answering 3 quizzes in 238 seconds and wins:  an Amazon Kindle.

Second Place

Rob van Wijk placed second with a score of 1154 answering 3 quizzes in 285 seconds and wins:  a $100 Amazon.com gift card.

Winners of O'Reilly Media Oracle PL/SQL bundle: Oracle PL/SQL Programming 5th edition, Oracle PL/SQL Best Practices 2nd edition, Oracle PL/SQL Pocket Reference, 4th edition

Bjoern Rost placed 3 with a score of 1110 answering 3 quizzes in 419 seconds

poelger placed 4 with a score of 1104 answering 3 quizzes in 440 seconds

Patrick Wolf placed 5 with a score of 1098 answering 3 quizzes in 459 seconds

John W. Schultz placed 6 with a score of 1095 answering 3 quizzes in 286 seconds

Justin Cave placed 7 with a score of 1084 answering 3 quizzes in 498 seconds

Winners of Oracle PL/SQL Programming 5th edition

Ralf Koelling placed 8 with a score of 1073 answering 3 quizzes in 532 seconds

Greg Belliveau placed 9 with a score of 1061 answering 3 quizzes in 566 seconds

Iloon Ellen placed 10 with a score of 1031 answering 3 quizzes in 476 seconds

Alison placed 11 with a score of 940 answering 3 quizzes in 376 seconds

Jonathan Hart placed 12 with a score of 877 answering 2 quizzes in 368 seconds and wins

Winners of $50 Amazon.com giftcard

John Jeunnette placed 13 with a score of 797 answering 3 quizzes in 462 seconds

shra1 placed 14 with a score of 766 answering 2 quizzes in 702 seconds

Winners of your choice of O'Reilly Media Oracle PL/SQL bundle: Oracle PL/SQL Programming 5th edition, Oracle PL/SQL Best Practices 2nd edition, Oracle PL/SQL Pocket Reference, 4th edition (some books may not be available)

Craig Robinson placed 15 with a score of 725 answering 3 quizzes in 496 seconds

Nuno Ornelas placed 16 with a score of 716 answering 3 quizzes in 520 seconds

Fuad Arshad placed 17 with a score of 714 answering 3 quizzes in 155 seconds

Roel Hartman placed 18 with a score of 607 answering 3 quizzes in 27033 seconds

steve aho placed 19 with a score of 601 answering 3 quizzes in 697 seconds

Rob Dawson placed 20 with a score of 436 answering 2 quizzes in 193 seconds

phowells placed 21 with a score of 342 answering 2 quizzes in 295 seconds

Texas placed 22 with a score of 302 answering 2 quizzes in 595 seconds

KevinZhang placed 23 with a score of 268 answering 2 quizzes in 698 seconds

TigerFan placed 24 with a score of 237 answering 2 quizzes in 1117 seconds

Coleman Leviter placed 25 with a score of 219 answering 2 quizzes in 287 seconds

Ara placed 26 with a score of 217 answering 1 quizzes in 99 seconds

Eric Tegenfeldt placed 27 with a score of 215 answering 1 quizzes in 104 seconds

John Flack placed 28 with a score of 207 answering 1 quizzes in 128 seconds

Lori  Townsend placed 29 with a score of 204 answering 2 quizzes in 138 seconds

Gonzalo  Segarra placed 30 with a score of 155 answering 1 quizzes in 104 seconds

PeaellSlashSeaquell placed 31 with a score of 148 answering 1 quizzes in 307 seconds

Dennis Ruane placed 32 with a score of 143 answering 1 quizzes in 142 seconds

C. Scyphers placed 33 with a score of 140 answering 2 quizzes in 1261 seconds

diggitydog placed 34 with a score of 121 answering 1 quizzes in 387 seconds

mnrbradley placed 35 with a score of 40 answering 2 quizzes in 272 seconds

Update: Quizzes and Prizes During Oracle Develop

We've been spreading the word and developers are taking the quiz - here in San Francisco and of course beyond.

I've also gotten a number of emails raising concerns about awarding prizes to players who submit answers in astonishingly short amounts of time. So I thought I would address this issue, and more on the blog.

First and most important: if you are here in San Fran and taking the quiz, do not forget to check the box under the Submit Answer button. You will not be eligible for a special prize unless you do so. All you have to do is check the box ONCE over the three days, and you will be eligible. So if you forgot Mon and Tue, no worries. Just check the box today. Please do NOT check it if you are not here. You will distort the rankings and prize allocations. Please contact us if you did this and I will uncheck the box for you.

Second, I will not award prizes this week to players with answer times that are below the reasonable minimum I have set for these questions. And, by the way, my analysis so far indicates that no more than one or two of the people here in San Fran might even possibly fall under that category.

World-wide, it is a different situation, but please do not worry: I will not give away prizes to people who clearly do not deserve it!

Warm regards,
Steven Feuerstein

20 September quiz: Whose Version Is It, Anyway? (1386)

The 20 September quiz on the Oracle Database 11g result cache feature generated several complaints regarding the Oracle version that applies to PL/SQL Challenge quizzes. Several people felt that the quiz violated the assumptions, which state that an Oracle Database 10g Release 2 instance should be assumed. But as I have pointed out before, all those assumptions are valid "unless otherwise mentioned" - that is, unless I state otherwise in the quiz. At the very top of the "Take the Quiz" page, you now see a line that says: "Oracle Version: Oracle Database 10g Release 2, unless noted otherwise in quiz" And then there is the actual text for the quiz. In the 20 September quiz, the very first sentence of the quiz stated: "I create and populate the following table on an Oracle Database 11g instance:" So, I would think that it is fairly clear that this quiz established an exception to the assumptions: this was an Oracle Database 11g question. As for the content of the quiz, several players also questioned what I show as the correct choice. I address these below. 1. "As far as I know, the result_cache implementation is different on 11g R1 and 11g R2, on which there exists no relies_on clause; result_cache structure is not mature enough yet, there are side effects, exceptions etc..." Now, I am really unclear as to what point the player is making regarding the maturity level of the feature - so please, Bora, elaborate on this blog. As for the different implementations on 11.1 and 11.2 - you are absolutely right. In 11.2, the RELIES_ON clause is deprecated; it is ignored by the PL/SQL engine. But it is still valid syntax, and so is fine for both 11.1 and 11.2 - I did not need to specify a "sub-release" for the quiz. 2. "Query on the Quiz of 20 September. I would have expected the correct answer to be: 1 Mouse Keyboard 100 Keyboard that's why I replied that none of the answres were correct The first DBMS_OUTPUT.put_line (name_for_num (1)); will give 1 Mouse (cache not used, as it's the first call) Then DBMS_OUTPUT.put_line (name_for_num (100)); will output: Keyboard (No execution of the function here, cache is used, therefore DBMS_OUTPUT.put_line (num_in); within the function is not executed) And, you have explained, the last call will output 1 Mouse But maybe there is something I am missing?" It is a bit hard to understand the above paragraph (Philippe, please clarify on the blog if you would like), but the main point to take away from this quiz is that if you make an uncommitted change to a table in your session, then any cache that relies on that table is not available to you until you commit or rollback. Thus, each call to the function in this quiz resulted in the body of the function being executed. The first two populate the cache since the function hadn't yet been called with those inputs, the third time because an update had occurred on plch_parts. 3. "Dear Mr. Feuerstein, I use Oracle 11.2.0.1 and I have tested my answer connected as SYSTEM. The result was: 1 Mouse 100 Keyboard Keyboard After I have seen at my score, the result was wrong, I tested the same on Oracle 11.1.0.7 connected as SYSTEM and I got an error in the function: PLS-00999: implementation restriction (may be temporary) RESULT_CACHE may not rely on system tables. As I have connected as another user than SYSTEM, I got the results you pretend on both systems: 1 Mouse 100 Keyboard 100 Keyboard I'm really sad about the outcome of the Qiuz :(" Markus, this is very interesting. I don't do development or try things in the SYSTEM schema, and I was not aware that Oracle had this restriction - so thanks for teaching me (and now us) something I didn't know. As for the behavior in 11.2 in SYSTEM, I cannot explain that. Can you please try the same thing in a non-SYSTEM account and see if your answer disagrees with mine? Having said that, I don't think that your experience invalidates the quiz, though I suppose I could add as an assumption that the code of the quizzes is never run in SYS or SYSTEM. I just worry about making our list of assumptions longer and longer - it will begin to look like the fine print of a contract, written by a lawyer! Regards from Oracle Develop / San Francisco, Steven

Forgiveness Policy Applied for First Time

Dear PL/SQL Challenge Players,

I just applied the forgiveness policy to your rankings for the first time. Since the final policy is different from that posted on the Rules page (I will update it as soon as possible), I thought I would share with you what I did this weekend:

1.I did another round of corrections for "too fast" answers. Some people may find their rankings well below what they were on Friday. Remember: take your time, read the questions carefully and thoroughly - and you will not see such a change happen to you.

2. I adjusted the forgiveness policy and applied it as follows:

For up to ten days in a quarter, we will adjust a player's quiz record as follows:

• A missed day will not count as a zero score; instead you will be given a score equal to 2/3 of the average of all your previous answers in the current quarter. In other words, we will pretend you took the quiz and performed at 2/3 of your usual level. I do not give you 100% of your average because that led to too big a boost in the scorings, effectively penalizing those players who did not miss any quizzes.
• A zero score will be replaced with a score equal to 2/3 of the average of all your previous answers in the current quarter. See explanation in first point.
• If you missed or scored 0 points for less than 10 days (let's refer to the balance of those days as "FD" or "forgiveness days"), then we will identify the FD # of days with the biggest delta between your weighted score and the maximum possible weighted score (you had a bad day). We then add to your weighted score for that day 75% of the difference between the maximum possible and your score. 

I know, I know - it's complicated. I don't want it to be complicated, but I want more than anything else for this policy to be fair. And that means that someone who missed 10 quizzes should not be able to leap above players who have played each day.

Finally, the adjustments that I made today for "bad days" or lowest rankings are only provisional. I will run these adjustments again at the end of the quarter (you may have even "worse" days ahead), but this will at least give you a sense of how the forgiveness policy will affect you. If I waited till the end of the quarter to apply any of these lowest ranking adjustments, those who missed lots of quiz days would suddenly be ranked well ahead of others who played each day and scored at a similar level.

So....again thanks for your patience as I apply increasingly sophisticated rules to the quiz data, to handle increasingly "subtle" requirements from you, the players.

Warm regards,
Steven

Prizes You Can Win During Oracle Open World Week

Here's a recap of all the prizes you can win this week playing the daily quiz. The first table shows what you could win if you are attending Oracle Open World or Oracle Develop. The second table shows the fantastic prizes available to anyone playing around the world.

Good luck to everyone!

For those attending Oracle OpenWorld or Oracle Develop:

Rank	Prize
1	Amazon Kindle ebook reader ($260)
2	$100 Amazon.com giftcard
3-7	O'Reilly Media Oracle PL/SQL bundle: Oracle PL/SQL Programming 5th edition, Oracle PL/SQL Best Practices 2nd edition, Oracle PL/SQL Pocket Reference, 4th edition ($120 value)
8 - 12	Oracle PL/SQL Programming 5th edition ($70 value)
13-14	$50 Amazon.com giftcard
Raffle	5 Oracle PL/SQL Programming 5th edition 15 Oracle PL/SQL Best Practices 2nd edition 15 Oracle PL/SQL Pocket Reference, 4th edition

For those not attending Oracle OpenWorld or Oracle Develop:

Rank	Prize
1	Complimentary registration to the ODTUG Kaleidoscope 2011 conference, a US$1775 value.
2-3	Complimentary registration in your choice of the Oracle PL/SQL Programming/APEXposed conferences, a US$600 value
Raffle	Complimentary membership in the ODTUG organization, a US$175 value