08 December 2014

Ideas for PL/SQL Championship(s) next year

For most of this year, we have offered two weekly, competitive quizzes on PL/SQL:

PL/SQL Challenge & PL/SQL Explore

But it doesn't really make sense to offer two different championships. At least, that's how I, the fellow who would write all those quizzes, feels about it.

There is, as one might expect, lots of overlap between the top 50 ranked players in the two quizzes: only 14 in PL/SQL Explore are not in PL/SQL Challenge's top 50.

It seems to me that it makes much more sense to have a single championship for PL/SQL, overall.

But our database design doesn't easily allow us to make that change (that is, one championship per domain with merged rankings).

So here is what I would like to do instead:

For the 2014 PL/SQL Championship, participants will be as follows:

1. The top 50 ranked players in the PL/SQL Challenge
2. The additional 14 in the top 50 of PL/SQL Explore
3. Up to 20 other wildcard and correctness players, all drawn from the PL/SQL Challenge

Then going forward, even though PL/SQL Explore will remain competitive, the PL/SQL Championship will be based only on your performance in the PL/SQL Challenge quiz.

What are your thoughts on this?

Thanks, Steven Feuerstein

01 May 2014

New Roundtable discussion: What's missing from PL/SQL?

PL/SQL has been around for over twenty years. It has been enhanced steadily and strongly throughout all that time, adding bulk processing, object types, automatic optimization, PL/Scope and so much more. So the question is: is PL/SQL fully baked? What is it missing that you need in order to write better code faster?

Give us your thoughts on the PL/SQL Challenge Roundtable.

PL/SQL is a critical enabling technology for hundreds of thousands of mission-critical applications around the world. Any improvement in the language can have a widespread impact. The PL/SQL development team must have a list of enhancement requests 10 miles long (no, I haven't seen it) and it is likely that anything we come up with here will already be on their list. But that's not the point. The point is to find out what active PL/SQL developers think is key for the language.

Some of you may be aware that I published the iloveplsqland.net website years ago to allow developers to sort-of-vote on enhancements to the PL/SQL language. It never generated a whole lot of traffic, but I encourage you to check it out for ideas. And if you posted an idea there, and still feel strongly about it, then post the info here as well!

I can think of several categories of enhancements to PL/SQL and I encourage you to let us know into which category your idea would fall.
  • Performance: improve the runtime performance of PL/SQL code.
  • Usability: make it easier or more productive to write your program units.
  • Multimedia/Internet: improve the way PL/SQL "plays" in the modern world of mobile and Internet applications.
  • Critical bug fixes: sure, it's not an enhancement, but if there's something that's been bugging you for years and still isn't fixed, go ahead and gripe about it here!
  • Object orientation: object types have come a long way....but assuming you are trying to use them to build production apps, what's missing?

Oh, and I need to be really clear about this: Just because I work for Oracle Corporation now, that doesn't mean that I can guarantee any of your ideas will be implemented. Heck. I don't even work in the PL/SQL development team (and I sure don't belong there!).

This is a discussion which will benefit enormously from every player using the Like feature to indicate which idea they, ahem, like. Please take advantage of it!

Finally, I point out to you that it would be best if you did not reply here with your ideas. Go to the PL/SQL Challenge Roundtable and do it. But I will be happy to read your thoughts here as well.

Cheers, Steven Feuerstein

18 April 2014

Another quiz idea for your consideration: Code Pong

Thanks for all your feedback on the PL/SQL Countdown quiz. I am certain we will add that to the mix soon.

And since I have decided to no longer keep all my best ideas to myself ( :-) ), I offer another idea for a "quiz", though it is not really a quiz, I suppose. More of a "game"?

I call it Code Pong (some of you may know of the classic Pong game). Here's how it would work:

It starts with a piece of rather awful code (likely a function or procedure that is supposed to do X or display certain output). It has all sorts of issues: outright bugs, typos, logic flaws, etc.

Two people sign up to play. And they each take turns applying changes to the code to improve it. The rules for making a change are:

1. You can change 1 line.
2. You can insert up to 3 lines.
3. You can delete up to 3 lines.
4. You can reject the other player's last move.
5. Assert that the game is done.
6. Accept or reject other player's assertion that game is done.

Player must include a description of the change: how does it help get closer to a solution?

What this means is that you can't make one big wholesale change to the code to fix it. Instead, you must work with the other player, sometimes following their lead, sometimes leading the way, to make changes until you believe the code works and all the problems are removed (that is, it not only works, but also is now a program that is easy to understand and maintain.

Here's a simple example: Change the code so that "9" is displayed.
   l_index NUMBER;
   FOR rec IN (SELECT * FROM all_source WHERE rownum < 10)
      l_index := l_index + 1;

Move 1 (player 1):

Change line 2 to:

l_index PLS_INTEGER;

Explanation: PLS_INTEGER is faster than NUMBER and you do not need NUMBER.

Move 2 (player 2):

Change line 2 to:

l_index PLS_INTEGER := 1;

Explanation: the default initial value is NULL and adding 1 to NULL returns 1. So l_index stays NULL throughout.

Move 3 (player 1):

Assert that game is done.

Move 4 (player2):

Accept; game is done!

What do you think? Too complicated? Too strange?

13 April 2014

Take a Survey on Database Design Quizzes

Chris Saxon, who is the "domain admin" for the Database Design and author of many of the weekly quizzes, would love to get feedback from our players on these quizzes. He's been doing them now for six months and figures it's time to check in with all of you.

So he's used Google Forms to set up a quick, anonymous survey:


We hope you can take a few moments to fill it out.

21 March 2014

Idea for a new type of quiz: Countdown PL/SQL

Always looking for ways to increase the entertainment value of the site, I came up with this idea last night.

Countdown PL/SQL

You have thirty seconds to answer ten true-false questions. 

With the clock on the page counting down, you are shown a statement about PL/SQL and you must quickly decide: true or false.

Of course, the same could be done with SQL, APEX, etc.

One concern I could see with this quiz is that comfort with the English language could have a big impact on how quickly you can get through the questions. It is also possible that we could offer versions that contained code, not (mostly) words.

Perhaps it is time to look at localization of content: offer it in different languages!

So what do you think? Sound interesting? Sound like something you'd bother playing?

17 March 2014

Q2 2014 Brings Big Changes to the PL/SQL Challenge

Last year, I (Steven Feuerstein) floated the idea that I would end the daily PL/SQL quiz. Many players protested, and several agreed to help me with the workload by writing quizzes. So I agreed to keep the daily quiz going into 2014 and we would see how it goes.

It went pretty well. Some players did write quizzes (a big thank you most especially to Jeroen Rutte, who wrote a total of 7 quizzes, 5 of which were used in Q1). But I still spent and spend lots of time writing quizzes, which I enjoy greatly, but unfortunately is not sustainable.

So starting in Q2 2014, the daily PL/SQL quiz will end and in its place we will offer three weekly quizzes in its place:

PL/SQL Challenge

The PL/SQL Challenge replaces the daily PL/SQL quiz that ran from April 2010 through March 2014. I will write all the quizzes for the PL/SQL Challenge quiz. They will focus on "core" PL/SQL features (including commonly-used supplied packages like DBMS_OUTPUT and UTL_FILE), up through the latest production version of  the Oracle database. Players who take this quiz are eligible to qualify for the annual PL/SQL Challenge Championship.[We may decide to increase the frequency of those championships, but for now: annual.]

PL/SQL Explore 

The PL/SQL Explore quiz explores the nooks and crannies of the PL/SQL language, and the many PL/SQL packages supplied by Oracle Corporation. Quizzes are provided by both Steven Feuerstein and players, and can range from "core" PL/SQL functionality to "edgy" elements of the language, like calling Java from PL/SQ or rarely used supplied packages. like DBMS_XA.

PL/SQL Deja Vu 

PL/SQL Deja Vu offers quizzes from the past - PL/SQL quizzes previously played on the daily PL/SQL quiz or one of the other PL/SQL quizzes. Use Deja Vu PL/SQL to reinforce your PL/SQL knowledge without worrying about competing with others. These quizzes are not ranked!

We will also "re-brand" the daily PL/SQL quiz to the "PL/SQL Challenge", so the daily quiz names will be of this form:

PL/SQL Challenge #276 D2011-04-12

and the weekly PL/SQL Challenge quiz names will look like:

PL/SQL Challenge #1240 WS2013-04-12

where "WS" is the "Weekly quiz Starting on".

We will look into ways to offer rankings across the various competitive PL/SQL quizzes, but we will not be able to get to that immediately.

Of course, the new quarter has not yet started. I am very interested to hear your feedback on these quizzes.

And, of course, you are welcome to shower me with flattery about how much you love those daily quizzes and want them to continue. Surely, my ego could use the stroking. :-)

But this time the decision is made: the daily quiz will end, and three new PL/SQL weekly quizzes will take its place.

10 March 2014

Results of 2013 Q4 PL/SQL Championship

You will find below the rankings for the 2013 Q4 PL/SQL championship; the number next to the player's name is the number of times that player has participated in a championship.

Congratulations first and foremost to our top-ranked players:

1st Place: Peter Auer of Germany

2nd Place: Mike Pargeter of United Kingdom

3rd Place: Frank Schmitt of Germany

This was Peter's first championship. An impressive rookie performance, Peter!

Next, congratulations to everyone who played in the championship. I hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes next week through the Practice feature. We will also make the championship as a whole available as a Test, so you can take it just like these players did.

My thanks to all my fine reviewers, but most particularly Elic, whose impact on the quality of our daily quizzes and championships cannot be overstated.

Steven Feuerstein

Note: Below the table of results for this championship, you will find another list showing the championship history of each of these players.

Rank Name Country Total Time % Correct Total Score
1Peter Auer (1)Germany27 mins 25 secs92%2882
2Mike Pargeter (12)United Kingdom22 mins 06 secs87%2788
3Frank Schmitt (8)Germany30 mins 05 secs87%2723
4Karel Prech (3)Czech Republic32 mins 25 secs87%2652
5Michal Cvan (11)Slovakia33 mins 58 secs87%2551
6Zoltan Fulop (7)Hungary29 mins 14 secs82%2485
7Janis Baiza (9)Latvia22 mins 35 secs76%2443
8Jerry Bull (11)United States29 mins 20 secs74%2443
9Ravshan Abbasov (1)Uzbekistan33 mins 11 secs79%2441
10Anna Onishchuk (8)Ireland16 mins 40 secs79%2432
11dmitrysk (1)Russia32 mins 52 secs84%2398
12Manfred Kleander (1)Austria34 mins 52 secs84%2393
13Vincent Malgrat (7)French Republic32 mins 35 secs79%2388
14Lukasz Kubicki (1)Poland22 mins 54 secs71%2362
15james su (8)Canada21 mins 52 secs71%2348
16Chad Lee (10)United States33 mins 00 secs84%2325
17Jeroen Rutte (7)Netherlands34 mins 46 secs82%2320
18Frank Puechl (4)Germany20 mins 50 secs74%2298
19Rytis Budreika (1)Lithuania18 mins 20 secs68%2253
20mentzel.iudith (13)Israel33 mins 13 secs84%2251
21João Barreto (4)Portugal22 mins 17 secs74%2214
22Telmoc (2)Portugal20 mins 30 secs66%2190
23Randy Gettman (12)United States34 mins 52 secs71%2183
24Yuri Pedan (4)Ukraine17 mins 06 secs71%2163
25Viacheslav Stepanov (12)Russia31 mins 00 secs76%2145
26Krzysztof Helbin (3)Poland26 mins 08 secs68%2112
27Anil Jha (3)United States34 mins 56 secs76%2101
28Kevan Gelling (9)Isle of Man30 mins 37 secs68%2078
29Siim Kask (13)Estonia27 mins 48 secs63%2054
30Leszek Grudzień (2)Poland30 mins 20 secs66%1998
31Yuan Tschang (8)United States34 mins 24 secs55%1972
32Niels Hecker (14)Germany35 mins 00 secs71%1960
33Oleksiy Varchyn (2)Norway21 mins 32 secs61%1919
34_tiki_4_ (6)Germany25 mins 06 secs58%1678
35Goran Stefanović (5)Serbia24 mins 33 secs61%1544
36Rakesh Dadhich (5)India26 mins 50 secs55%1443
37Stelios Vlasopoulos (10)Belgium24 mins 13 secs50%1406
38Thierry Poels (7)Belgium28 mins 05 secs58%1343
39Milibor Jovanovic (5)Serbia32 mins 24 secs53%1272
40Dan Kiser (6)United States33 mins 38 secs50%897

Championship Performance History

After each name, the quarter in which he or she played, and the ranking in that championship.
Name History
Peter AuerQ4 2013:1st
Mike PargeterQ4 2010:22nd, Q1 2011:15th, Q2 2011:8th, Q4 2011:5th, Q1 2012:5th, Q2 2012:17th, Q3 2012:5th, Q4 2012:20th, Q2 2013:12th, Q4 2013:2nd
Frank SchmittQ4 2011:22nd, Q2 2012:2nd, Q3 2012:21st, Q4 2012:3rd, Q1 2013:13th, Q2 2013:1st, Q3 2013:9th, Q4 2013:3rd
Karel PrechQ3 2012:36th, Q4 2012:23rd, Q4 2013:4th
Michal CvanQ3 2010:22nd, Q4 2010:25th, Q3 2011:21st, Q1 2012:11th, Q3 2012:14th, Q4 2012:14th, Q1 2013:14th, Q2 2013:11th, Q3 2013:1st, Q4 2013:5th
Zoltan FulopQ1 2012:15th, Q2 2012:26th, Q3 2012:22nd, Q4 2012:17th, Q1 2013:22nd, Q3 2013:22nd, Q4 2013:6th
Janis BaizaQ2 2010:3rd, Q4 2010:7th, Q3 2011:9th, Q4 2011:1st, Q3 2012:23rd, Q1 2013:4th, Q2 2013:4th, Q3 2013:3rd, Q4 2013:7th
Jerry BullQ2 2011:32nd, Q3 2011:8th, Q1 2012:12th, Q2 2012:11th, Q3 2012:13th, Q4 2012:15th, Q1 2013:9th, Q2 2013:15th, Q4 2013:8th
Ravshan AbbasovQ4 2013:9th
Anna OnishchukQ1 2011:5th, Q2 2011:20th, Q3 2011:19th, Q4 2011:17th, Q1 2012:21st, Q2 2012:8th, Q4 2013:10th
dmitryskQ4 2013:11th
Manfred KleanderQ4 2013:12th
Vincent MalgratQ4 2011:9th, Q1 2012:13th, Q2 2012:13th, Q4 2012:6th, Q1 2013:1st, Q2 2013:14th, Q4 2013:13th
Lukasz KubickiQ4 2013:14th
james suQ3 2010:56th, Q2 2011:7th, Q3 2011:11th, Q4 2011:14th, Q2 2012:15th, Q1 2013:7th, Q3 2013:15th, Q4 2013:15th
Chad LeeQ2 2011:26th, Q3 2011:17th, Q4 2011:12th, Q1 2012:1st, Q2 2012:23rd, Q3 2012:28th, Q4 2012:22nd, Q1 2013:20th, Q2 2013:8th, Q4 2013:16th
Jeroen RutteQ3 2010:19th, Q3 2012:10th, Q4 2012:11th, Q1 2013:8th, Q2 2013:18th, Q3 2013:4th, Q4 2013:17th
Frank PuechlQ3 2012:26th, Q1 2013:15th, Q3 2013:12th, Q4 2013:18th
Rytis BudreikaQ4 2013:19th
mentzel.iudithQ4 2010:4th, Q1 2011:17th, Q2 2011:23rd, Q3 2011:5th, Q4 2011:4th, Q1 2012:7th, Q2 2012:16th, Q3 2012:33rd, Q4 2012:4th, Q1 2013:2nd, Q2 2013:7th, Q3 2013:2nd, Q4 2013:20th
João BarretoQ3 2010:18th, Q4 2010:21st, Q2 2011:3rd, Q4 2013:21st
TelmocQ4 2013:22nd
Randy GettmanQ3 2010:8th, Q1 2011:25th, Q2 2011:10th, Q3 2011:3rd, Q4 2011:11th, Q1 2012:18th, Q2 2012:19th, Q3 2012:30th, Q4 2012:12th, Q1 2013:3rd, Q2 2013:13th, Q4 2013:23rd
Yuri PedanQ3 2010:45th, Q4 2010:9th, Q1 2011:2nd, Q4 2013:24th
Viacheslav StepanovQ1 2011:8th, Q2 2011:4th, Q3 2011:13th, Q4 2011:19th, Q1 2012:17th, Q2 2012:9th, Q3 2012:6th, Q4 2012:21st, Q1 2013:18th, Q2 2013:22nd, Q3 2013:11th, Q4 2013:25th
Krzysztof HelbinQ1 2012:10th, Q4 2012:18th, Q4 2013:26th
Anil JhaQ2 2012:28th, Q4 2012:19th, Q4 2013:27th
Kevan GellingQ2 2011:19th, Q3 2011:1st, Q4 2011:6th, Q1 2012:6th, Q2 2012:22nd, Q4 2012:16th, Q1 2013:12th, Q4 2013:28th
Siim KaskQ1 2011:28th, Q2 2011:6th, Q3 2011:10th, Q4 2011:3rd, Q1 2012:8th, Q2 2012:4th, Q3 2012:31st, Q4 2012:10th, Q1 2013:11th, Q2 2013:16th, Q3 2013:17th, Q4 2013:29th
Leszek GrudzieńQ2 2013:24th, Q4 2013:30th
Yuan TschangQ2 2012:24th, Q3 2012:24th, Q4 2012:27th, Q2 2013:30th, Q4 2013:31st
Niels HeckerQ2 2010:2nd, Q3 2010:1st, Q4 2010:15th, Q1 2011:6th, Q3 2011:7th, Q4 2011:10th, Q1 2012:2nd, Q2 2012:3rd, Q3 2012:2nd, Q4 2012:7th, Q1 2013:6th, Q2 2013:3rd, Q3 2013:13th, Q4 2013:32nd
Oleksiy VarchynQ3 2013:28th, Q4 2013:33rd
_tiki_4_Q4 2011:26th, Q1 2012:16th, Q2 2012:20th, Q4 2012:8th, Q3 2013:8th, Q4 2013:34th
Goran StefanovićQ1 2012:31st, Q2 2012:27th, Q3 2012:34th, Q4 2013:35th
Rakesh DadhichQ2 2012:10th, Q1 2013:23rd, Q2 2013:28th, Q3 2013:27th, Q4 2013:36th
Stelios VlasopoulosQ4 2010:37th, Q4 2011:20th, Q1 2012:27th, Q2 2012:30th, Q3 2012:1st, Q4 2012:1st, Q1 2013:27th, Q2 2013:23rd, Q3 2013:19th, Q4 2013:37th
Thierry PoelsQ3 2011:22nd, Q1 2012:24th, Q1 2013:26th, Q3 2013:25th, Q4 2013:38th
Milibor JovanovicQ4 2012:29th, Q1 2013:30th, Q3 2013:18th, Q4 2013:39th
Dan KiserQ4 2013:40th