23 July 2010

Daily quiz becoming a trivia contest?

I received this note today from a player:

"I feel that the pl/sql challenge is starting to become either a trivia contest or is starting to use too many poor programming practices to try to prove points about understanding. I don't envy you trying to come up with questions. A good programmer would never write code like today (22nd July) and wouldn't get into that position where it was difficult to tell how many rows would be returned after calling autonomous code or using savepoints/rollbacks. Thanks for the quiz so far, but fear I will soon be starting to drift away."

I would like to hear what others think. You are right, it is hard to come up with lots of good quiz questions. And, most definitely, I make no claims to only offer "best practice" code in the  quizzes. They are intended more to test/expand your knowledge of areas of the language.

For example, on July 22, I wanted to test your knowledge of the use of both SAVEPOINT and autonomous transactions to give you some control over how much of your changes are rolled back.

Perhaps I ended up obscuring the lesson within all that code? Perhaps my approach of using these "substitution" comments is not helpful to people?

So, sure, you wouldn't necessarily write code as it is shown in all the quizzes , but I am not sure why that is a barrier to getting something out of a given quiz.

I also hope to start including best practice questions - but those can be tough because of the level of subjectivity in that whole idea: what is "best" to me might be far from obvious or even acceptable to you.

Start a PL/SQL "programming competition"?

I just received this note from Niladri Biswas:

I am an active participant of the PL SQL Challenge . Basically I am a SQL Server developer but also shares a lot of interest in Oracle which I started not very long. PL SQL Challenge forum is a great place to learn and hone our skills. At least it is helping me.

I would like to make one suggestion. Along with the current challenge idea, if you can organize PL SQL programming competition then it will be more attractive. I basically set up questions in the TSQL beginners challenge(Current one is #12) with collaboration with Mr. Jacob Sebestain and I am also an active participant of TSQL Challenge (Current on is #34).

He and I agreed: I would post this on the blog and invite comment, so please comment!

Here is my initial feedback: I have definitely thought about this in the past (either me posting a challenge such as "Write a function that parses a delimited string." or posting a problem a developer has, and seeking a solution). I have two concerns:

1. If it's a competition, how do you score and rank people? It's one thing to answer a multiple choice quiz. It's quite another to verify that a program is correct or well-written etc.

2. How can this be done without consuming an enormous amount of someone's time? Believe me, the Challenge is already chewing up large chunks of my life. :-)

The nice thing about this could be a "crowd-development" dynamic, posting a solution, having it critiqued and improved, get general agreement on optimal solution, then make available as reusable code for everyone.

SF

22 July 2010

Questions raised regarding 21 July quiz on SQLERRM(1246)

The 21 July quiz asked you to choose which statements about SQLERRM were correct. Several players wrote to me about the following: a. I offer as one of the correct choices: "This function is defined in the STANDARD package, one of two default packages in the PL/SQL language." One person wrote: " I didn't check this answer because I was in the opinion there are a lot more then two default packages (all the dbms_* and utl_* packages)." There is a big difference between a default package and a built-in package. A "default package" means that if you do not have to include the package name in the reference to the element in that package. For example, I can write a WHEN clause like "WHEN NO_DATA_FOUND" and do not have to use "WHEN STANDARD.NO_DATA_FOUND". If Oracle cannot resolve the reference to NO_DATA_FOUND in the block in which it is referenced, Oracle will automatically try to resolve it against one of the two default packages, STANDARD and DBMS_STANDARD. Having said all that, I now realize that Oracle does not seem to use this term to describe STANDARD or DBMS_STANDARD. I also now have another item to add to my checklist on quiz reviews: "Do not include extraneous information." I could have just said that SQLERRM is defined in STANDARD, and left it at that. So I am undecided: should this warrant a re-scoring, in which everyone receives credit for this choice? Or should you chalk this up as a learning experience about PL/SQL? While I chew over this question, I would like to hear what you think. b. Several people believed that you can only call SQLERRM without an argument while inside an exception handler. This is not true. You can call it anywhere, without an argument. Outside of an exception section, it will always show the same message "ORA-0000: normal, successful completion" - precisely because SQLCODE is 0 outside of an exception handler. Other concerns or comments?

21 July 2010

Change to Statistics Page and sorting

We have turned off the ability to change sort order of columns in reports on the Statistics page.

This feature in some cases caused an error to be displayed:

ORA-01785: ORDER BY item must be the number of a SELECT-list expression

You should no longer see this error, but the price to be paid, at least for now, is loss of custom sorting capability.

Questions raised about 22 July quiz on "repeat until"(1236)

I have received emails from several players raising questions about the 22 July quiz. Four of the 1408 players who took the quiz noticed and reported a typo in the question itself. I asked:
A "repeat until" loop executes the loop body and then checks the control expression of the loop to see if the loop should continue executing (that is, as long as that expression evaluates to TRUE, the loop executes again). Which of the following statements about the "repeat until" loop is true for PL/SQL?
The problem is that the word "TRUE" should be "FALSE." That is, my statement of the definition of a "repeat until" loop is wrong. I believe that the vast majority of you did not notice, because you knew what "repeat until" meant and so you just went right on to the answers. So, first of all, Dirk will receive an O'Reilly media ebook as a prize. Second, after consulting with those who reported the issue, I have decided to not change scores and rerank for all players. As noted above, it is my belief that virtually everyone took the quiz as was intended. If you noticed this mistake, decided it was a "trick question" and answered "all wrong" as a result, please contact me at steven@stevenfeuerstein.com and your score will be adjusted. Next, a number of you complained about my scoring the following choice as correct:
PL/SQL does not offer a "repeat until" loop. You can instead emulate this behavior in a while loop as follows (assume that do_stuff is defined and called correctly, and sets the value of terminating_condition to TRUE or FALSE, never to NULL):
DECLARE  
  terminating_condition BOOLEAN := FALSE;
BEGIN
  WHILE (NOT terminating_condition)
  LOOP
     do_stuff (terminating_condition);
  END LOOP;
END;
Here is a typical protest: "One of the answers is given that we could use a WHILE LOOP to emulate Repeat Until. However it is to be noted that WHILE would validate the terminating condition at the start of the loop itself, but for repeat until the loop would be executed at least once even thought the terminating condition is true. In that sense that we cannot use WHILE LOOP to exactly emulate REPEAT UNTIL." And here is a very nuanced voicing of the same concern: "While I completely agree that the choice using WHILE will always execute the body of the loop at least once, I do not agree that this is an “emulation” of “REPEAT UNTIL” since the condition is evaluated before the body of the loop is executed. This feels like a “trick” question since the statement of the question specifically mentions evaluating the condition after the loop. If the wording of the WHILE choice had said “You can instead execute the body of the loop at least once by using WHILE as follows…” then I would agree that it is a correct choice." Here is my view on this: "emulate" means to me that when you run it, it will behave in the same way as the "original" formulation. WHILE loops, in general, are not like REPEAT UNTIL, in that the expression is checked before the loop body is executed. In the choice shown above, however, it is constructed so that the body of the loop is executed (and always executed once, since terminating_condition is initialized to FALSE), and then the terminating condition is checked. It seems to me that the behavior of this usage of WHILE matches what you would get if you could use a REPEAT UNTIL formulation in PL/SQL. Was it tricky? Apparently so, though I must admit that I did not intend for it to be tricky. I do believe, however, that it (the choice as it is worded) is a correct answer to the question, as it is worded. Please offer your thoughts on this.

20 July 2010

19 July 2010 quiz: some concerns raised by players(1233)

The 19 July quiz addressed the question of how to disable a trigger on a table. Here are the two comments I have received: "While I know that the answers are correct for the July 19th, 2010 quiz, I think the answer is a bit misleading. You can disable triggers with the ALTER TABLE command , you can't specify a specific trigger, but you can say 'ALTER TABLE disable all triggers' " "It is not clear if today quiz is well formulated - while ALTER TRIGGER sometrigger DISABLE / is obviously valid it is not PL/SQL, rather plain SQL." Addressing the second point, yes, certainly this is not PL/SQL per se. It is, however, a common task for developers, in my experience, and therefore worthy of testing on the PL/SQL Challenge. What are your thoughts?

19 July 2010

Problems with reminder emails?

I just received this very disturbing bug report:

"I continue to be shocked by the carelessness of the developers of this site. I have unsubscribed from email notifications multiple times simply because the developers made a change. Yet again, I get a notification, simply because they added 2 reminders, and defaulted a reminder to 12:00 instead of "No reminder", even though I was not subscribed. Laziness, incompetence, lack of thorough testing, whatever - it's unbecoming of a website dedicated to good programming."

Ugh. No pulled punches there!

We are looking into possible bugs in the reminder logic. In the meantime, if anyone else is getting reminders when they feel they have turned them off, please send me an email at steven@plsqlchallenge.com letting me know of this.

Thanks (and sorry for any inconvenience), SF

18 July 2010

Winners of First PL/SQL Challenge Playoff Announced

On 15 July 2010, 32 developers participated in the first-ever PL/SQL Challenge championship. This playoff was the culmination of a three-month competition in which PL/SQL developers from around the world tested their knowledge of PL/SQL against one another through daily quizzes.

And now the results (drumroll, please!):

First Place: Gerben Kroese (Netherlands)
Second Place: Niels Hecker (Germany)
Third Place: Janis Baiza (Latvia)

4: John Hall (United States)
5: Timur Akhmadeev (Russia)
6: Nick Strange (United States)
7: Rob van Wijk (Netherlands)
8: Ken Holmslykke (Denmark)
9: Rich Dellheim (United States)
10: Jeff Kemp (Australia)

11: Michal Pravda (Czech Republic)
12: Henrikas Zukovskis (Lithuania)
13: Edwin van Meerendonk (Netherlands)
14: Marcus Matzberger (Germany)
15: heho (Germany)
16: Muhammad Abdul Halim (Bangladesh)
17: shwetamber kaushik (India)
18: Javid Sch (Azerbaijan)
19: Eurico Matos (Portugal)
20: Filipe Silva (Portugal)
21: Jeyhun Gasimov (Azerbaijan)
22: Adriano Teixeira (Portugal)
23: Toine van Beckhoven (Netherlands)
24: Anton Scheffer (Netherlands)
25: Sailaja Pasupuleti (India)
26: Nopparat Vanichrudee (Thailand)
27: Radoslav Golian (Slovakia)
28: Latha (United States)
29: Fabio Sangalli (Italy)
30: Noor Ahmed (India)
31: Christian Rokitta (Netherlands)
32: Rokas Baltuskonis (Lithuania)

Congratulations, first and foremost, to Gerben, Niels and Janis on your accomplishments. A very impressive performance on what participants reported was a tough set of PL/SQL quizzes.

Gerben wins US$1000, or another prize of equal or greater value (Gerben's choice).

Niels wins US$500, or another prize of equal or greater value (Niels's choice).

Janis wins a "library" of all of O'Reilly Media's Oracle ebook, or another prize of equal or greater value (Janis's choice).

Congratulations also to everyone else in the top 10. You each win your choice of an O'Reilly Media ebook.

Everyone else who participated will receive a certificate verifying your participation in the playoff, which you I hope you will display prominently, as it demonstrates your knowledge of and commitment to learning more about the PL/SQL language.

We will soon add a number of reports to the Statistics page so that you can get a sense of scores, ranking, country distribution, and more, for this playoff.

Announcement from Finn Ellebaek Nielsen

To all PL/SQL Challenge players

Having played a key role in the PL/SQL Challenge project almost since the beginning and having contributed significantly in delivering the first vital milestones, namely the initial release, UI redesign and the first quarterly championship, I now feel that it's time to turn my focus back to my own business on automated Oracle code testing, http://oracletesting.com.

I have been involved at all levels of this project, including business planning, feature planning, design, implementation of PL/SQL + SQL backend, APEX front end (including HTML, CSS and JavaScript), quiz reviews and the quizmaster role. It has been a good opportunity to work with all in the team and I've learnt a few new tricks on the way. Thank you very much to the growing number of players that support the PL/SQL Challenge and all the valuable feedback you've provided, thanks to the team for all your hard work and thank you very much to Steven for offering me this unique opportunity in the first place.

I wish the project every success and the team all the very best of luck in the future.

All the best,
Finn