Two Years of the PL/SQL Challenge, and Looking Ahead

The second anniversary of the PL/SQL Challenge's launch date (8 April) approaches. Two years of daily quizzes, and so much more. Excellent time to reflect on how things have gone and how they can be improved in the future.

Three-plus years ago, I had this wild idea: that PL/SQL developers might like to take quizzes – even daily quizzes, both to learn more about PL/SQL and also to demonstrate to the world (well, mostly to other PL/SQL developers) how much of an expert they are in this database programming language.

Turns out I was right, since two years after starting the PL/SQL Challenge, thousands of developers have played these quizzes, submitting over 450,000 answers to hundreds of quizzes. Along the way, I learned many lessons about what it takes to write high-quality, error-free, interesting and non-trivial quizzes, and to provide a website where players could compete and be ranked. 

I offer below some thoughts on the PL/SQL Challenge and also alternative ideas for competitive play (the daily PL/SQL quiz) and international rankings. I would very much like to hear your own ideas, as well as any feedback you have on mine.

First, What's Coming
We will soon be releasing version 2.2 of the PL/SQL Challenge, with these major new features:

Activity Points

Up through 2.1, the only reflection of your activity on the PL/SQL Challenge was your ranking in the daily PL/SQL quiz, or some other quiz. Of course, the vast majority of players will never find themselves in the top rankings for these quizzes, but they nevertheless spend lots of time and effort at the PL/SQL Challenge improving their skills. In 2.2, we now award points to players for all of their activities on the site (taking quizzes, submitting quizzes, reviewing results, engaging in discussions about quizzes, etc.).

Practice Quizzes

Prior to 2.2, you could take a quiz when it is initially offered, and then review results and answers through the Library afterwards. You could not, however, take past quizzes if you'd missed them, no could you re-take a quiz. Now, you can set up practices based on past quizzes, specific features about which you want to learn more, or your favorite authors. You can also use the unique Autotune feature: the PL/SQL Challenge will automatically create practices for you, based on pre-defined criteria.

Quizbook: Quiz as PDF

One of the most long-sought enhancement requests on the PL/SQL Challenge, the Quizbook feature makes it easy for you to generate a PDF document of one or more quizzes. These can be formatted as an offline test (no answer information included), a player scorecard (showcasing your performance) or a knowledge document, containing all answers and resources. You can save and reuse your Quizbooks, as well as sharing them with other players.

On-Site Discussions

Rather than discuss interesting aspects of quizzes on the PLSQL-Challenge.Blogspot.com blog, you can now start or answer a thread directly on the Quiz Details page. You can also ask for help (it must be related to the topic of the quiz) or even post an objection to the quiz. In other words, if you think there is something wrong with the quiz, you can register your concern on both the post-quiz feedback page or on the Quiz Details page. You can also easily see if another player has already submitted a request for a correction.

Lessons Learned

Here are some of those lessons:

• You can't write a good quiz in five minutes. Maybe 30 minutes, when you include the time it takes to write verification code, test that code, etc. More like an hour. So, as you might well imagine, I have spent many hours over the past several years writing quizzes (and lots of other developers have, too, since over 200 quizzes have been submitted by others!).
• Many developers are ready to take the time and make the effort to play each day, but many more probably don't have the time or commitment or discipline to do any more than play occasionally, and use the website to look over quizzes and learn lessons from them.
• Moving from passive learning (reading documentation or books) to active learning through competition helps identify all sorts of nuances, bugs and areas for improvement in the technology covered by the quizzes.
• It is extremely difficult to come up with a way to offer quizzes that makes cheating impossible, but also extremely difficult to prove that anyone actually is cheating.
• It's hard work to support and maintain a website that is available 24x7. Oh yeah.
• A formal education in music theory is an excellent foundation for people who would like to become programmers. I have been very impressed and proud of how my son, Eli, has been able to come up to speed on SQL, PL/SQL, APEX, JavaScript, CSS and more.
• There's so much about the Oracle technology stack and running websites that I do not know. The PL/SQL Challenge could never have survived as long as it has without the help of my friends at Apex Evangelists.
• I sometimes wonder how long I can keep up the flow of five new quizzes each week that are of sufficient quality to be used as a way to rank PL/SQL developers.

Certainly, I could just keep the PL/SQL Challenge going the way it has, but I think that it is worth, after two years of publishing daily quizzes (and more), to contemplate alternatives to the daily quiz and, more broadly, public ranking through competition.

Variations on the Daily Quiz

I offer below ideas for variations on the daily PL/SQL quiz.The motivation behind these variations is that I want (a) lots more people to play quizzes generally and (b) lots more people to compete.

• Three new quizzes a week: rather than offer a new quiz each weekday, cut back on the volume by publishing a new quiz on, say, Monday, Wednesday and Friday. I will have to provide 40% fewer quizzes and players will not have to commit as much of their time to compete. The big question for players is: how important do you think it is to have a new quiz each day?
• Keep doing a daily quiz, but offer a mix each week of three new quizzes and two "used" quizzes – played sometime in the past (at least six months, probably, so that they will not be fresh in anyone's mind).
• Rather than post a new quiz each day, publish all the new quizzes (whether it be three or five) on, say, Sunday. They must be completed by the following Saturday, but you can choose when to take the quizzes. You might have a few hours available on Tuesday afternoon, so you do all the quizzes in one sitting.

Making Rankings Count

Here are some ideas to minimize the chance of cheating and improve the credibility of rankings:

• Rather than offer rankings on all quizzes taken, define only certain quizzes as competitive. Each player makes a decision to compete and to do so, you must register with a credit card, pay a small fee. If a person wants to compete under two accounts, they will have to go to much greater lengths to hide their identity (and they'll also have to pay more money). Sure it is still possible, but much less likely, I believe.
• For all other quizzes, you take them, you accumulate points for the effort, but you are not ranked. One advantage of making this distinction is that we can publish many more quizzes that will help you deepen your expertise (and highlight the knowledge of others), since not all quizzes will have to meet the criteria needed for competition. For example, "quick" true/false quizzes don't work well for the daily quiz, but can be very handy in reinforcing knowledge or exposing gaps.
• Your real name (that provided on your credit card, which must match your registration information) will be displayed in rankings. You can't "hide" behind a player name.
• Zero tolerance for aberrant scoring: everyone takes qualifier quizzes to verify their performance during the previous month (and, likely, for the quarterly championship as well). Highly aberrant patterns (such as extremely fast and extremely accurate) that cannot be reproduced lead to lifetime expulsion from the competition.'

Your Thoughts?

• What have you learned from the PL/SQL Challenge? 
• What do you like best about it? 
• How do you think it can be improved? 
• How can we get thousands of Oracle technologists to play? 
• Why don't your co-workers play now? 
• What can be done to get your manager to see the value of the PL/SQL Challenge, and actively encourage all members of the team to participate?

Many thanks in advance,
Steven Feuerstein

Over 450,000 Answers to 681 Quizzes!

Today, the PL/SQL Challenge hit another milestone: players have submitted over 450,000 answers to N quizzes since April 2010.

 We just passed a big milestone at the PL/SQL Challenge: over 450,000 answers have been submitted for the over 680 quizzes offered since its inception in April 2010. Here are some stats:

The Stats	Wow!
Number of Registrations	10,603
Number of Players	8,740
Quizzes Played	681
Answers Submitted	450,424
Time Spent Answering Quizzes	1,053 days
Average Time Per Quiz	03 mins 39 secs
Prize Money Awarded	$40,097

Many thanks to all the players who have:

• Taken the time to play consistently over the last two years;
• Submitted quizzes for play; 
• Reviewed quizzes to help improve their quality (tremendously!);
• Spread the word about the PL/SQL Challenge.

And thanks also to my friends at Apex Evangelists, who played a key role in getting the PL/SQL Challenge up and running, and who help us keep it going.


Steven Feuerstein

New Qualifier Process for Playoffs

We will be instituting a new process for the upcoming Q1 2012 championship playoff.

All or some of the players who qualify to participate in the playoff will be required to take three qualifier quizzes.

If the performance on these qualifier quizzes is substantially worse than the player's performance during the quarter (% correct and/or time required to answer the quiz), then the following steps will be taken:

1. That player will be ineligible to participate in the playoff.
2. That player's status will be set to non-competitive.
3. Any awards from the previous quarter will be reversed.

We believe that this qualifier step will strengthen the integrity of the playoff and ensure the best possible results for all players.

Warm regards,
Steven Feuerstein

Optimization of Cursor FOR Loops and Implicit Queries (11588)

The 8 February 2011 quiz tested your knowledge of the performance implications of different ways of fetching a single row of data, ranging from the use of OPEN FOR with a cursor variable to a cursor FOR loop.

The key objective of the quiz was to make sure you were aware that when you open a cursor variable, Oracle always performs a parse.

But we also scored as incorrect the choice that stated:

The PL/SQL optimizer "rewrites" the cursor FOR loop implementation (plch_use_cfl) so that the procedure executes a single, implicit query instead.

And two players objected. The procedure referenced is:

CREATE OR REPLACE PROCEDURE plch_use_cfl (
   id_in IN plch_employees.employee_id%TYPE)
IS
   l_employee   plch_employees%ROWTYPE;
BEGIN
   FOR rec IN (SELECT *
                 FROM plch_employees
                WHERE employee_id = id_in)
   LOOP
      l_employee := rec;
   END LOOP;
END;
/

The objections are as follows:

1. The fourth quiz option states that the last function (that uses a FOR loop over a query) will be rewritten to issue a single query. I marked this as "Correct" because I think I know what you're saying; but I don't think the option is really worded correctly. The FOR loop, even without any compiler optimisation, will only execute 1 query - but without optimisation, it may require multiple FETCHes to get the rows; whereas with the optimisation, it will FETCH 100 rows at a time.

2. I think that the wording of the quiz was somewhat incorrect, because it used several times the term "implicit query" or even "single implicit query", while probably meaning "implicit cursor" and fetching all the rows at once. As far as I am aware, there exists no such thing as "implicit query". A FOR cursor loop using FOR rec IN (SELECT ... ) LOOP ... is already an implicit cursor, as opposed to using CURSOR my_cursor IS SELECT ... followed by FOR rec IN (my_cursor) LOOP ... which is an explicit cursor. So, if we interpret the entire quiz from the performance point of view, then the last choice (No. [9490]) can be interpreted also as: "The compiler will rewrite the cursor FOR loop to achieve a performance similar to that of an implicit cursor" and such an interpretation renders the choice as correct in the context of this quiz. The optimizer will "rewrite" the cursor FOR loop to use a BULK COLLECT of an array of size 100, which, for our case (returning one single row) will have the same performance as using an implicit cursor ( same as a SELECT ... BULK COLLECT INTO ... that returns all the rows at once ). Again, not an "implicit select" but an "implicit cursor", in what concerns performance.

As usual, a choice composed of words instead of code leads to issues of interpretation. I thought I'd worded this choice so that it was clearly not true. And I still believe that. OK, my response:

Regarding the comments in (1), you are right that the choice is not worded correctly...to warrant marking the choice as correct. Both players correctly understand that a cursor FOR loop is optimized to fetch up to 100 rows at a time, instead of single rows. Even putting aside the issue of what "implicit query" means (more on that below), the optimizer does not re-write that code

Regarding "implicit query": yes, that was sloppy language. I should have been more, ahem, explicit. The following is what I had I meant:

The PL/SQL optimizer "rewrites" the cursor FOR loop implementation (plch_use_cfl) so that the procedure executes a SELECT...INTO statement instead of a loop.

I hope everyone will agree that this is false. That is not what the compiler does. OK, so then is my original formulation ambiguous and in need of re-scoring? I don't think so...because:

a. I expect that most players did interpret my phrase "implicit query" to be a SELECT...INTO. In other words, while not as precise as it should have been, it was precise enough.

b. But what if you wanted to interpret that phrase ("implicit query") literally? The second player writes that "As far as I am aware, there exists no such thing as 'implicit query'." Actually, I did a search on the phrase and found this:

An implicit query is a component of a DML statement that retrieves data without using a subquery. An UPDATE, DELETE, or MERGE statement that does not explicitly include a SELECT statement uses an implicit query to retrieve rows to be modified. 

So Oracle does define the implicit query, though not in a way that I had expected, and clearly not in a way that would lead to one interpreting this choice to be correct.

Your thoughts?

Winner Selected for Hierarchical Queries Challenge (8471)

In October 2011, we posted a competition regarding hierarchical queries, with this introduction:

Here at the PL/SQL Challenge, we recently encountered the need to accumulate counts of questions up through our topics tree (explained further below). With the help of Kim Berg Hansen, we came up with a solution. In the process, however, Kim realized that (a) the first pass may not be the most optimal solution and (b) he also had a more complex scenario, with which he could use some help. So we decided to offer up this challenge to our players. Remember: you can come back to this problem as often as you'd like. You do not need to submit an answer right away!

Several dozen players submitted solutions, many of them obviously taking lots of time and effort on their parts. Kim went through all the proposed solutions and evaluated them. His analysis is available in the answer for this competition, as well as the "All Submitted Answers" archive.

He chose Milan Vontorcik as offering the best solution for the "complex" scenario and he has been awarded a prize of an O'Reilly Media ebook.

Thanks to all players who submitted a solution to this puzzle.

Steven Feuerstein

Different error handling behavior between EXECUTE IMMEDIATE and DBMS_SQL (11296)

One of the Q4 2011 playoff quizzes examined the way that user-defined exceptions are handled. If you didn't participate in the playoff, you may want to view this quiz - even try to answer it for yourself - before reading this post.

Iudith Mentzel, who placed 5th in the playoff, took a closer look at the handling of user-defined exceptions raised in a dynamic PL/SQL block - and discovered something odd: the behavior when native dynamic SQL (EXECUTE IMMEDIATE) was used is different from that of DBMS_SQL. Check it out....and let us know if you have an idea as to why this is happening.

1. Create a package with two user-defined exceptions.

CREATE OR REPLACE PACKAGE plch_pkg
IS
   e1   EXCEPTION;
   e2   EXCEPTION;
END;
/

2. Try to catch the exception with native dynamic SQL and it goes unhandled:

BEGIN
   EXECUTE IMMEDIATE 'BEGIN RAISE plch_pkg.e2; END;';
EXCEPTION
   WHEN plch_pkg.e1
   THEN
      DBMS_OUTPUT.put_line ('e1 caught');
   WHEN plch_pkg.e2
   THEN
      DBMS_OUTPUT.put_line ('e2 caught');
END;
/
BEGIN
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 1
ORA-06512: at line 2

3. But with DBMS_SQL, it is trapped:

DECLARE
   c   INTEGER := DBMS_SQL.open_cursor;
   s   INTEGER;
BEGIN
   DBMS_OUTPUT.put_line ('Parsing ...');
   DBMS_SQL.parse (c, 'BEGIN RAISE plch_pkg.e2; END;', DBMS_SQL.native);

   DBMS_OUTPUT.put_line ('Executing ...');
   s := DBMS_SQL.execute (c);
EXCEPTION
   WHEN plch_pkg.e1
   THEN
      DBMS_OUTPUT.put_line ('e1 caught');
      DBMS_OUTPUT.put_line ('status=' || s);
      DBMS_OUTPUT.put_line ('sqlcode=' || SQLCODE);
   WHEN plch_pkg.e2
   THEN
      DBMS_OUTPUT.put_line ('e2 caught');
      DBMS_OUTPUT.put_line ('status=' || s);
      DBMS_OUTPUT.put_line ('sqlcode=' || SQLCODE);
END;
/
Parsing ...
Executing ...
e2 caught
status=
sqlcode=1

This is very interesting and unexpected (to me). Do any of you have any ideas on what might be causing this?

Thanks to Iudith for another fascinating exploration!

Cheers, SF

Q4 2011 Championship Playoff Results

You will find below the rankings for the Q4 2011 playoff; the number next to the player's name is the number of times that player has participated in a playoff. Congratulations first and foremost to our top-ranked players:

1st Place: Frank Schrader, Germany, wins an Amazon.com US$250 Gift Card.

2nd Place: Janis Baiza, Latvia, wins an Amazon.com US$175 Gift Card.

3rd Place: Valentin Nikotin, Russia, wins an Amazon.com US$100 Gift Card.

There are several results worthy of special comment:

1. Frank Schrader not only had the highest score, but actually got 100% of the quizzes right, the only player to do so. Very impressive, Frank! But even more impressive is that Frank won first place in the Q1 and Q3 2011 playoffs as well. In other words, Frank has placed 1st in 3 of 4 quarterly championships this year.

2. Valentin Nikotin pursued an interesting strategy. He completed the entire competition in just over 7 minutes, less than half that of almost all players. His % correct was "only" 86.2% (as compared to, say, that of Siim Kask with 96.6%, who placed just after him at 4th), which was enough to propel him to third place.

3. Vincent Malgrat participated in his first playoff, and broke into the top ten. Nice work, Vincent!

We have upgraded the Winners page to show you not only the rankings and results of all playoff participants (click on the All Playoff Prizes and Rankings button), but also make it easy for you to compare the players' performance in the playoff with that of the quarter.

Congratulations to everyone who played in the playoff. I hope you found it entertaining, challenging and educational.

Steven Feuerstein

Rank	Name (# of Playoffs)	Country	Total Time	Total Score
1	Frank Schrader (6)	Germany	14 mins 22 secs	2963
2	Janis Baiza (4)	Latvia	22 mins 12 secs	2641
3	Valentin Nikotin (4)	Russia	07 mins 16 secs	2635
4	Siim Kask (5)	Estonia	24 mins 03 secs	2619
5	mentzel.iudith (5)	Israel	28 mins 44 secs	2375
6	Mike Pargeter (5)	United Kingdom	15 mins 08 secs	2372
7	Kevan Gelling (4)	Isle of Man	26 mins 55 secs	2372
8	Chris Saxon (3)	United Kingdom	15 mins 42 secs	2356
9	Jeff Kemp (7)	Australia	24 mins 33 secs	2354
10	Vincent Malgrat (1)	French Republic	23 mins 23 secs	2277
11	Niels Hecker (6)	Germany	25 mins 45 secs	2250
12	Randy Gettman (5)	United States	18 mins 39 secs	2217
13	Chad Lee (3)	United States	29 mins 39 secs	2172
14	John Hall (4)	United States	21 mins 01 secs	2155
15	james su (4)	Canada	22 mins 35 secs	2123
16	Joaquin Gonzalez (4)	Spain	14 mins 02 secs	2094
17	Dalibor Kovač (4)	Croatia	28 mins 10 secs	2077
18	Anna Onishchuk (4)	Ireland	27 mins 58 secs	2056
19	Andre van der Put (1)	Netherlands	12 mins 52 secs	1998
20	Viacheslav Stepanov (4)	Russia	19 mins 26 secs	1971
21	kowido (4)	No Country Set	29 mins 07 secs	1943
22	Stelios Vlasopoulos (2)	Greece	29 mins 54 secs	1872
23	Ninoslav Čerkez (2)	Croatia	29 mins 42 secs	1871
24	Frank Schmitt (1)	Germany	27 mins 38 secs	1847
25	Nina (1)	Russia	29 mins 24 secs	1667
26	Gideon Bruggink (1)	Netherlands	29 mins 56 secs	1596
27	Alain Boulianne (2)	French Republic	26 mins 40 secs	1587
28	_tiki_4_ (1)	Germany	25 mins 53 secs	1377
29	monpara.sanjay (1)	India	26 mins 28 secs	1346
30	Syed Ariful Bari (2)	Bangladesh	03 mins 20 secs	1018