29 January 2011

"Trickery!" cry some players re: 28 January quiz (1945)

Oh, this quiz definitely irritated some players - and some of the most devoted ones, at that. This quiz was tricky, no doubt about it. Here's the part of the code that played a role in the quiz:
   FOR r_managers IN (SELECT * FROM plch_employees)
      UPDATE plch_employees
         SET salary = salary * 2
       WHERE employee_id = r_managers.employee_id;
Notice there is no semi-colon after END LOOP. Many players submitted a bug report about this, but it is not a mistake in the quiz. Instead, since there is no semi-colon, what appears to be a "COMMIT;" statement is actually treated by the PL/SQL compiler as the label for the END LOOP statement.

When Sergey submitted this quiz, my first reaction was to say "Nah, that's just a trick; what does it teach you?" But after looking at it more closely and reading Sergey's explanation, I decided there was a useful lesson or two here:

1. Challenge your assumptions: this seemed like a classic example of a scenario in which your code is not compiling or there is some other bug, and you stare and stare at the code and cannot see the problem. Usually this is because you make an assumption, it goes unexamined, unrecognized, and it is wrong.

2. Use your IDE formatters! Using Toad, SQL Navigator, PL/SQL Developer? They all have powerful formatters to help you read and understand your code. When I reformat the above code, it turns into:
   FOR r_managers IN (SELECT * FROM plch_employees)
      UPDATE plch_employees
         SET salary = salary * 2
       WHERE employee_id = r_managers.employee_id;
   END LOOP commit;
And now I can see clearly the problem.

So I am very sorry that some of you felt tricked (see comments below), but (a) I do think it was an interesting and worthwhile exercise and (b) remember, it's just one of many and I truly doubt they'll be many like this!

Comments from Players

"I would have rated this an "Advanced" question - based on the results it is obvious not many people (myself included) knew that you can omit the label before the loop. Myself, I would probably have interpreted the missing semicolon as a typo. If I'd known the difficulty level was not Basic, I would have looked for more than just "what is the result of COMMIT and ROLLBACK within a procedure"."
"I think deliberately missing a semi-colon to give unexpected result, without making it obvious that had been missed is very much cheating. As is rather obvious from the ludicrously low proportion of people who got it right. And I expect most of te people who did get it "right" actually did so for the wrong reason."

"The quiz on January 28, 2011 was more of a "trick" than a test of one's PL/SQL knowledge. The code was intentionally formatted to obfuscate its intent. I didn't notice the missing semicolon, and thus answered incorrectly; however, it has been stated repeatedly that one should not expect such trickery from the PL/SQL Challenge. I am extremely disappointed that such a question was accepted."

"I could imagine myself happening this to me in real life while debugging a code and me spending an hour or so to figure out what was going wrong. And finally submitting this as a quiz on PL/SQL Challenge. And getting delighted to see only 7% of all players could get it right)). To my opinion this quiz tests more of your debugging skills than PL/SQL knowledge. I am not complaining in any way but saw this type of quiz first time on PL/SQL Challenge so thought of sending a note. I would be more careful in reading the code now onwards PS: When I encountered this quiz it look so straightforward to me that in spite of knowing the correct answer (which was wrong of course) I tempted to actually write a sample code and test it before submitting my answer. I did that and confirmed that my answer was indeed right but it was wrong of course because I tested my sample code with semicolon on."

"While I'm not really in a position to cry foul with the 28/1/11 quiz, it does seem a little against the typical tone of the quizzes. Sure, there is a lesson to be learnt here, but I'm not entirely sure this was the way to teach it. No doubt you will get a few comments about this particular quiz, I'd be interested on your thoughts."

"Very un-funny trick in the quiz. You "forgot" the semi-colon after "end loop" so the compiler considers the commit a loop label. After all, "COMMIT" is not a reserved word as you have previously pointed out. I guess you wanted to check who would actually know and understand this feature. I can see how it could be a devious problem to debug. In a timed competition, however, I would have to call the question unfair. It seems particularly so since someone who truly does not understand transaction control might choose this answer because they are "double wrong." I enjoy your competition highly and appreciate that you put in all the effort it must take. I feel questions like this taint the experience. I look forward every week day to the Challenge. I hope to be able to continue for a long time to come."

28 January 2011

"Create" not the same as "define"? Players cry "Ambiguous!" (1944)

In the 27 January quiz, I asked: "Which of the choices show the successful creation of a nested table type?" I then showed choices that created a nested table type in the database as a schema-level object, and also defined a type inside a PL/SQL block. I marked all these choices as correct.

Several players complained that this was ambiguous, that the use of the word "creation" implied a create in the database. Feel free to read their comments below.

When this criticism was first brought to my attention (late last night), my initial reaction was: "C'mon, you know what I meant." But as more players chimed in with the same objection, I have decided I need to rescore.

So I will adjust the text of the question a bit, give everyone credit for the two choices that are PL/SQL blocks, and recalculate scores and ranks.

My apologies for the careless phrasing.

Cheers, SF

Player Comments

"I felt the use of "create/creation" in todays question to be ambiguous. I answered from the persective that only the "create or replace type" statements would actually create the nested table type. The anonymous pl/sql blocks (the valid ones at least) only declare a nested table type that is never used. To me create is not the same as declare - if the quiz had asked which statements defined or described a valid nested table type, then I would have felt the anonymous pl/sql blocks were options as well."

"I had a doubt on this quiz I am fairly sure about difference between varray, nested table and associative array - no ambiguities there... But the term "creation of a type" I am not quite sure if I have understood correctly. Normally I would only use "create a type" when I create a schema-level type. I would not use the word "create" if I merely defined a type in a pl/sql declaration section. But I do realize that you could easily argue that a type definition in a declaration section "creates" a pl/sql type (as opposed to create a schema level type.) So I ended up believing that probably other people would use "create" both for pl/sql types and schema level types, and probably I am wrong in thinking that "create" is just schema level types, and so I chose my answers accordingly. I'm not asking any rescores or anything like that - I just felt sufficiently in doubt that I thought I'd let you know that at least one player had "ambiguous doubts" about even this very simple sentence in this quiz - you may be prepared if others have the same doubts. (Or possibly I am so wrong about the understanding of "create" that I am the only one."

"I had a bit of confusion in Today's quiz because of the word "Creation" in the question. I knew that nested tables can be defined as SQL types as well as within PL/SQL programs. There were 3 choices showing correct definition of nested table types. However I have chosen only 1 (create or replace type..) as correct, because I thought "successful creation of a nested table type" means to say a type has to be "created". When you define/declare a type inside the package or pl/sql block, the type is not "created" (rather package is created). I really hope this is what you meant."

"I must object to the wording of the January 27th quiz. I understood CREATE as "define a type that remains in the database". The anonymous block option marked as correct does not do this, but rather uses a temporary nested table type for the duration of the block, which cannot possibly be used in any other context. A better wording of the quiz would be: "Which of the choices show valid uses of nested table types". Then I would agree that also the anonymous block is a correct choice. I can understand where you were trying to go with the quiz now that I see your answers, but maintain that the question is ambiguous."

26 January 2011

Challenge discussion leads to ERs for new warnings!

I am very pleased to announce that a discussion on the PL/SQL Challenge blog regarding the 26 November 2010 quiz has led to the submission by Bryn Llewellyn, PL/SQL Product Manager, of two enhancement requests to add new compile-time warnings! See details below.

My hope, plan and expectation is that as our community grows, as more and more developers participate, we will have an ever-greater impact on the evolution of the Oracle PL/SQL language.

Great work, everyone!

Cheers, Steven

Background and note from Bryn Llewellyn

I asked this question of Bryn (drawn from the quiz): "Suppose my procedure looks like this:
   (arg1 OUT varchar2, arg2 OUT varchar2) IS
   arg1 := 1;
   arg2 := 2;
I then call the procedure as follows:
   x VARCHAR2 (100);
   EXECUTE IMMEDIATE 'BEGIN twoargs( :v1, :v2 ); END;'
     USING OUT x, OUT x;
   /* Note original post showed OUT x, OUT l_value. I have fixed it! */
Is the value assigned to x indeterminate or does that "indeterminacy" only come into play when using NOCOPY?"

Bryn answered as follows:

In your example, you used the same actual for two different out formals. In other words, the same name for two different phenomena: outputs with different meanings and in general different values. That's why it's proper to call it an example of aliasing.

Here, the indeterminacy arises because the PL/SQL language definition makes no promise about the order in which out formals are written back to their actuals. (At least, if we published such a definition, that statement would be true.) Any particular program, running in any particular version of Oracle Database, will doubtless always do this writing back in the same order. But that might change on a Database version boundary. It might also change according to some code change you made that would seem inessential (going from two out formals to three, changing the datatypes of the formals, changing to a specific scalar collection element rather than an ordinary scalar variable for one the actuals -- who knows). So there's no experiment you can do to show the indeterminacy in question here. Just take our word for it.

Your use of execute immediate slightly disguised things. But the "using out" construct is just another twist on the formals/actuals picture. So, for that matter is this:
select Sum(Sal), Avg(Sal)
  into a, a
  from Scott.Emp;
Your question did cause some lively discussion here -- which broadened its scope a bit. As a result, I filed these two enhancement requests:

10636541 - Provide new PL/SQL warning when same variable used for >1 actual
10636525 - Provide new PL/SQL warning when a package spec exposes a variable

They both have status "Published = Y" so you should be able to read them via My Oracle Support -- and tell your quizzers (or is that quizzees) about them.

25 January 2011

Should the PL/SQL Challenge be constrained to documented features?

I received this note today regarding the 24 January quiz on overloading (in which I test your knowledge of the fact that you can overload a procedure and function with same name and parameter list):

I am registering a protest about the 1/24 quiz. I have used overloading before, but not having access to an Oracle instance in my current job I must rely on the documentation, so when I could not recall clearly if different types of programs qualified for overloading, it checked the documentation. The documentation reads "You can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family.". I even checked the definition of “formal parameters” and “actual parameters” to see if that included the program name and it does not appear to. I would like credit for the correct answer.

This is what I wrote back to the player:

I am disappointed to hear that the documentation does not include reference to or example of overloading that differs by program type - but I am not terribly surprised.

One thing I have learned from the Challenge is just how lacking the documentation can be. But I cannot organize the Challenge and its quizzes solely around what is available in the official documentation.

It is definitely a bummer that you have no access to an Oracle instance when you take the quiz. I couldn't really imagine taking the Challenge and hoping to do really well just based on my memory and the documentation (though you might want to supplement your doc checks with checking my books or other resources as well!).

But I do not believe that a rescoring is required in this situation.

I would like to hear what you think about this as well.

24 January 2011

Q4 2010 Championship Playoff Results

On 20 January, we held the playoff for Q4 2010. Thirty-nine players participated with the results shown at the end of this post. Congratulations to everyone who played this tough competition (as you can see from the % correct values), but especially to:

#1 ranked Dominic Brooks - winner of US$250 Amazon.com gift card
#2 ranked Gary Myers - winner of US$175 Amazon.com gift card
#3 ranked Justin Cave - winner of US$100 Amazon.com gift card

Players ranked 4th through 10th win an O'Reilly Media ebook.

All participants will receive a PDF certificate of participation and accomplishment.

Again, congratulations to all and best of luck to everyone in this new quarter!

Steven Feuerstein

Q4 2010 Playoff Results

Dominic Brooks (United Kingdom) #1: 4962 points / 10 quizzes / 1165 secs / 88.6% correct
Gary Myers (Australia) #2: 4751 points / 10 quizzes / 789 secs / 84.1% correct
Justin Cave (United States) #3: 4671 points / 10 quizzes / 1077 secs / 84.1% correct
mentzel.iudith (Israel) #4: 4645 points / 10 quizzes / 1138 secs / 81.8% correct
Pavel Zeman (Czech Republic) #5: 4311 points / 10 quizzes / 1138 secs / 77.3% correct
Kim Berg Hansen (Denmark) #6: 4239 points / 10 quizzes / 1127 secs / 77.3% correct
Janis Baiza (Latvia) #7: 4227 points / 10 quizzes / 941 secs / 75% correct
Urs Metzger (Germany) #8: 4124 points / 10 quizzes / 1025 secs / 75% correct
Yuriy Pedan (Ukraine) #9: 4108 points / 10 quizzes / 840 secs / 75% correct
Eigminas Dagys (Lithuania) #10: 4068 points / 10 quizzes / 1013 secs / 75% correct
Markus Langlotz (Switzerland) #11: 4041 points / 10 quizzes / 1155 secs / 75% correct
Zoltán Pásztor (Hungary) #12: 3926 points / 10 quizzes / 1171 secs / 70.5% correct
Binuraj  Nair (United Kingdom) #13: 3921 points / 10 quizzes / 1181 secs / 72.7% correct
Peter Schmidt (Germany) #14: 3904 points / 10 quizzes / 1159 secs / 72.7% correct
Niels Hecker (Germany) #15: 3889 points / 10 quizzes / 979 secs / 70.5% correct
Chris Saxon (United Kingdom) #16: 3844 points / 10 quizzes / 1008 secs / 70.5% correct
Elic (Belarus) #17: 3829 points / 10 quizzes / 1129 secs / 70.5% correct
Henrikas Zukovskis (Lithuania) #18: 3706 points / 10 quizzes / 1005 secs / 68.2% correct
Riccardo Butticè (Italy) #19: 3669 points / 10 quizzes / 961 secs / 65.9% correct
Filipe Silva (Portugal) #20: 3604 points / 10 quizzes / 1086 secs / 68.2% correct
João Barreto (Portugal) #21: 3587 points / 10 quizzes / 1059 secs / 65.9% correct
Mike Pargeter (United Kingdom) #22: 3547 points / 10 quizzes / 1103 secs / 65.9% correct
Rob van den Berg (Netherlands) #23: 3455 points / 10 quizzes / 1128 secs / 65.9% correct
Peter Hraško (Slovakia) #24: 3274 points / 8 quizzes / 1126 secs / 77.1% correct
Piet van Zon (Belgium) #25: 3256 points / 8 quizzes / 954 secs / 71.4% correct
Michal Cvan (Slovakia) #26: 3224 points / 8 quizzes / 1099 secs / 74.3% correct
siamnobita (Thailand) #27: 3139 points / 8 quizzes / 1130 secs / 68.6% correct
pinkal soni (India) #28: 2881 points / 8 quizzes / 1153 secs / 65.7% correct
Michael Meyers (United Kingdom) #29: 2833 points / 8 quizzes / 1118 secs / 65.7% correct
Uwe Küchler (Germany) #30: 2701 points / 7 quizzes / 1119 secs / 74.2% correct
Frank Schrader (Germany) #31: 2667 points / 9 quizzes / 1120 secs / 56.4% correct
Gunjan (India) #32: 2657 points / 8 quizzes / 1148 secs / 60% correct
Mariusz Kupczynski (Poland) #33: 2644 points / 10 quizzes / 922 secs / 50% correct
V Vandana Patel (India) #34: 2600 points / 8 quizzes / 1140 secs / 60% correct
Theo Asma (Netherlands) #35: 2506 points / 6 quizzes / 432 secs / 70.4% correct
Patrick Wolf (Austria) #36: 2348 points / 6 quizzes / 1130 secs / 74.1% correct
Niels Jespersen (Denmark) #37: 2338 points / 7 quizzes / 1085 secs / 64.5% correct
Boneist (United Kingdom) #38: 1645 points / 5 quizzes / 1140 secs / 63.6% correct
SteliosVlasopoulos (Greece) #39: 1203 points / 4 quizzes / 893 secs / 61.1% correct

23 January 2011

Help us Test Version 1.9 of PL/SQL Challenge

We have upgrade test.plsqlchallenge.com to 1.9 and invite you to help us test this release over the next week. We plan to upgrade our production site to 1.9 on 29 January. As you might expect with just a month since the upgrade to 1.8, this version does not offer major new features. Instead, we have added a number of "nice to have" features requested by players, as well as implemented many improvements to our administration pages.

You can log in using the same email/password you use on the production site. The quizzes shown this week are not the same as the real daily quiz. And remember: any data entered on the test site (user profile information, quizzes taken, etc.) will not be saved when we upgrade to 1.9 on www.plsqlchallenge.com. 

You will find below a list of new features and suggestions on how to use/test them. 

Single Correct Choice Quizzes
For those questions in which it is clear that at most one choice can be correct, you will now see different instructions, be only allowed to check one box (including "None of the above"), and will receive a score of either 0% or 100%, but nothing in between.The quizzes on 25 and 27 January are both defined as "single correct choice."

Players can now request an email with results of a specific quiz.
If the results for a specific day's quiz did not go out, if you missed it, if you simply want to go back and get an email with the updated quiz relates format (chagned in 1.8), you can now do so by drilling down to a quiz through the Past Quizzes page or Quizzes Taken in your profile. Then press the Email Quiz Results button.

Find Player
You can now search for players by name, country or ranking, and then view their profile.  You do not have to be logged in to do this.

Go directly from Quiz Survey page to Player Rankings page
After submitting a quiz, you can now ask to go directly to the Player Rankings page.

Display summary information from quiz surveys
You can now see a summary of quiz surveys after you drill down to a specific quiz through Past Quizzes or Quizzes Taken. We have also added charts to make it easier to visualize the results.

Recommendation descriptions are now displayedOn your public profile, any descriptions you provided for your recommendations are now displayed, along with the URL.

View Quiz on Same Day Taken
You can now view today's quiz after you have taken it through the Past Quizzes. You will not, as you would surely realize and expect, be able to see the answers or any explanatory text- until the next day.

Clear Quiz History
Not happy with your early days at the PL/SQL Challenge? Would you like to wipe the slate clean and "start over"? You can now clear out your quiz history prior to a date from the profile main menu page.

Review Submitted Quizzes
Players who submit quizzes for use on the PL/SQL Challenge can review them through the profile menu page. Simply click on "Review Submitted Quizzes" (currently at the bottom of the page). You can also send us comments about the quiz.

Copy Achievement
We've made it easier to enter multiple, similar achievements (say, if you'd written 10 books on PL/SQL :-) ).