Website Unavailable - 15:30 UTC 25 October

The PL/SQL Challenge website is having problems. We are investigating and plan to have it back online ASAP.

If you have not already done so, please follow PLSQLChallenge at twitter.com so you can receive updates on website status that way.

Q3 2012 Playoff on 24 October

The next quarterly championship playoff (for Q3 2012) will take place on Wednesday, 24 October. Forty-five players have qualified to take five tough quizzes in forty minutes. Wish them luck!

Here's some background on the players in the upcoming playoff, including the number of playoffs in which they've already played; their rank in that quarter, their rank in the playoff, and their best playoff performance.

Name	# Playoffs	Qtr Rank	Last Playoff Rank	Best Playoff
swart260	1	1/1454	24/33	Q2 2012 - 24th
Sean Stuber	5	2/1454	9/33	Q3 2010 - 5th
Jerry Bull	5	3/1454	14/33	Q3 2011 - 9th
Stelios Vlasopoulos	4	4/1454	33/33	Q4 2011 - 22nd
mentzel.iudith	7	5/1454	19/33	Q4 2010 - 4th
Vinu	1	6/1454	32/33	Q2 2012 - 32nd
Chad Lee	5	7/1454	26/33	Q1 2012 - 1st
Viacheslav Stepanov	6	8/1454	12/33	Q2 2011 - 4th
Randy Gettman	7	9/1454	22/33	Q3 2011 - 4th
Marco Siefert	1	10/1454	31/35	Q1 2012 - 31st
Frank Schrader	8	11/1454	3/33	Q1 2011 - 1st
Siim Kask	7	12/1454	6/33	Q4 2011 - 4th
Yuan Tschang	4	13/1454	27/33	Q2 2012 - 27th
kowido	6	14/1454	1/33	Q2 2012 - 1st
Justin Cave	6	15/1454	2/33	Q2 2012 - 2nd
Niels Hecker	8	16/1454	5/33	Q3 2010 - 1st
Anna Onishchuk	6	17/1454	11/33	Q1 2011 - 6th
Frank Schmitt	2	18/1454	4/33	Q2 2012 - 4th
Jeroen Rutte	1	19/1454	20/62	Q3 2010 - 20th
Chris Saxon	3	20/1454	8/29	Q2 2011 - 2nd
Zoltan Fulop	2	21/1454	29/33	Q1 2012 - 17th
Giedrius Deveikis	1	22/1454	7/33	Q2 2012 - 7th
koko	0	23/1454	N/A	N/A
Sebastian Kolski	2	24/1454	17/33	Q2 2012 - 17th
Kim Berg Hansen	3	25/1454	8/33	Q4 2010 - 6th
Janis Baiza	4	27/1454	2/29	Q4 2011 - 2nd
Markus Langlotz	1	29/1454	11/38	Q4 2010 - 11th
Ivan Blanarik	2	30/1454	15/33	Q1 2012 - 3rd
Jens Petersen	1	34/1454	N/A	N/A
Tony Winn	1	35/1454	17/62	Q3 2010 - 17th
Patrick Barel	0	37/1454	N/A	N/A
Jason H	0	46/1454	N/A	N/A
Goran Stefanović	2	50/1454	30/33	Q2 2012 - 30th
Michal Cvan	5	57/1454	12/35	Q1 2012 - 12th
Gary Myers	4	68/1454	1/34	Q2 2011 - 1st
Mike Pargeter	7	69/1454	20/33	Q4 2011 - 6th
puchtec	0	78/1454	N/A	N/A
Peter Schmidt	2	80/1454	14/38	Q3 2010 - 2nd
macabre	2	87/1454	27/31	Q2 2011 - 17th
Vijay Mahawar	0	99/1454	N/A	N/A
Tobias Stark	1	140/1454	35/35	Q1 2012 - 35th
Karel Prech	0	166/1454	N/A	N/A
mark kavalaris	0	274/1454	N/A	N/A
Dan Kiser	1	308/1454	N/A	N/A
Fernando Bautista	0	464/1454	N/A	N/A

Time for the Q3 2012 Championship Playoff!

The following players will be invited to participate in the Q3 2012 championship playoff. The number in parentheses after their names are the number of playoffs in which they have already participated.

It's very impressive to see the #1 rank taken by a player who has participated in just one previous playoff.  Six players have never been in a playoff before - it's always good to have "new blood" giving those repeat players a run for their money.

See the FAQ for an explanation of the three ways a player can qualify for the playoff. And congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

We will set and publish the date of the playoff very soon.

Name	Rank	Qualification	Country
swart260 (1)	1	Top 25	Netherlands
Sean Stuber (5)	2	Top 25	United States
Jerry Bull (5)	3	Top 25	United States
Stelios Vlasopoulos (4)	4	Top 25	Belgium
mentzel.iudith (7)	5	Top 25	Israel
Vinu (1)	6	Top 25	India
Chad Lee (5)	7	Top 25	United States
Viacheslav Stepanov (6)	8	Top 25	Russia
Randy Gettman (7)	9	Top 25	United States
Marco Siefert (1)	10	Top 25	Germany
Frank Schrader (8)	11	Top 25	Germany
Siim Kask (7)	12	Top 25	Estonia
Yuan Tschang (4)	13	Top 25	United States
kowido (6)	14	Top 25	No Country Set
Justin Cave (6)	15	Top 25	United States
Niels Hecker (8)	16	Top 25	Germany
Anna Onishchuk (6)	17	Top 25	Ireland
Frank Schmitt (2)	18	Top 25	Germany
Jeroen Rutte (1)	19	Top 25	Netherlands
Chris Saxon (3)	20	Top 25	United Kingdom
Zoltan Fulop (2)	21	Top 25	Hungary
Giedrius Deveikis (1)	22	Top 25	Lithuania
koko (0)	23	Top 25	Ukraine
Sebastian Kolski (2)	24	Top 25	Poland
Kim Berg Hansen (3)	25	Top 25	Denmark
Janis Baiza (4)	27	Wildcard	Latvia
Markus Langlotz (1)	29	Wildcard	Switzerland
Ivan Blanarik (2)	30	Wildcard	Slovakia
Jens Petersen (1)	34	Wildcard	Germany
Tony Winn (1)	35	Wildcard	Australia
Patrick Barel (0)	37	Wildcard	Netherlands
Jason H (0)	46	Wildcard	United States
Goran Stefanović (2)	50	Correctness	Serbia
Michal Cvan (5)	57	Correctness	Slovakia
Gary Myers (4)	68	Wildcard	Australia
Mike Pargeter (7)	69	Correctness	United Kingdom
puchtec (0)	78	Wildcard	Germany
Peter Schmidt (2)	80	Wildcard	Germany
macabre (2)	87	Correctness	Russia
Vijay Mahawar (0)	99	Correctness	India
Tobias Stark (1)	140	Correctness	Germany
Karel Prech (0)	166	Correctness	Czech Republic
mark kavalaris (0)	274	Correctness	No Country Set
Dan Kiser (1)	308	Correctness	United States
Fernando Bautista (0)	464	Correctness	United Kingdom

Should PL/SQL Developers Care About "Internals"?

We've posted a new Roundtable discussion with the title

Should PL/SQL Developers Care About "Internals"? 

This discussion point was provided Jeff Kemp, a long-time player of the PL/SQL Challenge who writes:

Our esteemed colleague Steven once wrote: "...regarding the question of "internal" storage: I have no idea how Oracle manages these structures. ... why should we care? We just need to know that we can use the data structures with confidence (no or minimal bugs, good performance). Why get caught up with 'internal' details that can change from version to version anyway?" Do you agree?

I encourage you to visit the Roundtable discussion page, read Jeff's full presentation, and add your thoughts.

PL/SQL Challenge Adds New Feature: Favorites

We upgraded to version 2.4 today and it has a single new feature - requested by many players: Favorites.

You can now "tag" authors, players, quizzes, resources, features and more as Favorites. The home page will then show latest news about your favorites and - if you are receiving emails with quiz results - you will be notified via a daily email digest of this news as well.

You will now see "Add to Favorites" links on the Quiz Details page, a player's profile, and more. Just look for this widget:


and click on it to add to your favorites, after which it will look like this:

 
You will now find on your home page a new section showing the latest news for your Favorites:




The Favorites tab on the menu offers access to the Favorites Manager page, where you can remove favorited items and also "zoom" straight to an item of interest.


And the Favorites Manager:
Notice that you can request to have a daily digest emailed to you with the latest news for your Favorites. We have turned on this preference only if you are already receiving daily emails with quiz results.

We hope you like this feature and take full advantage of it. Please let us know if you see any ways we can improve it.

Warm regards and happy playing,
Steven Feuerstein

September Update at the PL/SQL Challenge

[Find below the text of the September 2012 e-newsletter sent out to PL/SQL Challenge subscribers.]

Another summer gone...and a hot one it was. Let's just hope the ice in the Arctic circle returns next year. In the meantime, eyes turn towards Oracle Open World, the annual extravaganza of All Things Oracle (and that is an awful lot of "things", compared to the old days).

I'll be presenting twice on Wednesday, 3 October, and I will have more PL/SQL Challenge ribbons. So I hope to see many Challengers there and ready to add that ribbon to their conference badge!
Speaking of the old days, the 2012 OOW will be my twentieth consecutive presenting at OOW or IOUW (International Oracle User Week, the precursor to OOW that was organized by the International Oracle User Group). I just came across the acceptance letter for my 1992 submission: "An Interactive Debugger for SQL*Forms." Yes, that's right. A debugger for SQL*Forms 3.0, which I built in SQL*Forms itself. I called it XRay Vision and it was a very cool utility - and a testimony to the foolishness of building tools for products at the end of their lifecycle. I didn't sell too many copies of XRay Vision. Sigh...

We've now passed 550,000 answers submitted to quizzes since April 2012 - the PL/SQL Challenge is still going strong and making Oracle technologists around the world stronger PL/SQL developers. If you haven't been playing lately (or taking advantage of our Practice feature - more on that below), I encourage you to pay us a visit.

Dynamic SQL: Never use to avoid code repetition?

The 12 September 2012 PL/SQL quiz asked players to pick the choice that would allow a developer to add an entirely new table to the application schema, and not have to change the procedure that will be used to insert into this table. This is accomplished through the use of dynamic SQL, and several players objected to the use of dynamic SQL in this kind of situation. This quiz was drawn from our own experience at the PL/SQL Challenge. We are implementing support for Favorites and we have at this point seven "kinds" of favorites: quiz, author, player, roundtable discussion, quiz commentary, resource, feature. We have a distinct table for each kind of favorite, whose names follow the convention "qdb_fav_[type]s", as in qdb_fav_authors and qdb_fav_players.

I really like to minimize code volume and strip out any repetition, so I chose this dynamic SQL approach to inserting a now into the appropriate favorites table:

PROCEDURE add_favorite_dynamic (user_id_in         IN INTEGER,
                                favorite_id_in     IN INTEGER,
                                favorite_type_in   IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE
         'INSERT INTO qdb_fav_'
      || favorite_type_in
      || 's (user_id, '
      || favorite_type_in
      || '_id, notify_user) VALUES (:user_id, :favorite_id)'
      USING user_id_in, favorite_id_in;
END add_favorite_dynamic;

Notice that even if we start keeping track of another kind of favorite, this procedure would not have to be modified, as long at the naming conventions are followed when creating the new table.
If I were to use static SQL, my procedure would look like the one below, and would have to be changed each time we add a new favorites table:

PROCEDURE add_favorite_static (user_id_in         IN INTEGER,
                               favorite_id_in     IN INTEGER,
                               favorite_type_in   IN VARCHAR2)
IS
BEGIN
   CASE favorite_type_in
      WHEN c_fav_author
      THEN
         INSERT INTO qdb_fav_authors (user_id, author_id)
              VALUES (user_id_in, favorite_id_in);
      WHEN c_fav_comp_event
      THEN
         INSERT INTO qdb_fav_comp_events (user_id, comp_event_id)
              VALUES (user_id_in, favorite_id_in);
      WHEN c_fav_discussion
      THEN
         INSERT INTO qdb_fav_discussions (user_id, discussion_id)
              VALUES (user_id_in, favorite_id_in);
      WHEN c_fav_player
      THEN
         INSERT INTO qdb_fav_players (user_id, player_id)
              VALUES (user_id_in, favorite_id_in);
      WHEN c_fav_question
      THEN
         INSERT INTO qdb_fav_questions (user_id, question_id)
              VALUES (user_id_in, favorite_id_in);
      WHEN c_fav_resource
      THEN
         INSERT INTO qdb_fav_resources (user_id, resource_id)
              VALUES (user_id_in, favorite_id_in);
      WHEN c_fav_thread
      THEN
         INSERT INTO qdb_fav_threads (user_id, thread_id)
              VALUES (user_id_in, favorite_id_in);
      WHEN c_fav_topic
      THEN
         INSERT INTO qdb_fav_topics (user_id, topic_id)
              VALUES (user_id_in, favorite_id_in);
   END CASE;
END add_favorite_static;

Another static SQL approach: rather than having a distinct table for kind of favorite, use a single favorites table (and thereby lose the ability to define a foreign key on the "favorite ID" column):

PROCEDURE add_favorite_static (
   user_id_in         IN INTEGER,
   favorite_id_in     IN INTEGER,
   favorite_type_in   IN VARCHAR2)
IS
BEGIN
   INSERT
     INTO qdb_favorites (user_id, favorite_type, favorite_id)
   VALUES (user_id_in, favorite_type_in, favorite_id_in);
END add_favorite_static;

Which approach would you take? We've set up a poll to get your feedback on this issue. I hope you can take a few minutes from your busy schedule to "vote".

Practice Makes Expert!

Did you know that in addition to taking daily, weekly and monthly scheduled quizzes, you can also (re)take any past quizzes through the PL/SQL Challenge Practice feature?

Click on the Practice tab on the menu and you can then set up a practice based on particular features of the technology you'd like to get more familiar with or for quizzes drawn from favorite authors. You can also take advantage of the Autotune feature: the PL/SQL Challenge will automatically add practices to your queue based on either past low scores or missed quizzes - you decide how many of and how often these practices should be generated.

Since we added the Practice feature to the PL/SQL Challenge in April 2012, nearly 450 Oracle technologists have completed over 2100 practice quizzes. One player has taken 130 practice quizzes, and another twenty players have taken at least 25 quizzes each.

Here's what one player, Ruslan, told us about how he uses this feature:

"I like to practice my PL/SQL on PL/SQL Challenge because I can hone my knowledge in a wide range of themes and on different levels. Constant practicing helps me to solve my tasks much better and more effective and to prepare to be certified. For example when I started one of my first projects I knew nothing about BULK operations and Dynamic SQL and packages that hold constants. So I developed not really flexible and fast application. After I learned all of that here I rewrite that application and made it more general and fast enough. The PL/SQL Challenge site is very ergonomic. I like the Library because it helps me to get back to my mistakes and learn what I didn't know previously. And I like statistics. Statistics shows my progress in time. Thank you for such a great place to learn PL/SQL!"
 
You, too, can use the PL/SQL Challenge site to learn more about PL/SQL (and SQL and APEX....) and solve problems faster. Set up your Autotune practices today and start down the path to PL/SQL expertise!

New Poll: Usability of the PL/SQL Challenge website

An on-again, off-again player of the PL/SQL Challenge wrote the following to us:

"I tried to find how to play the PL/SQL quiz and just couldn't find where to go to play the PL/SQL quiz which I've played on and off for a few years now. The web pages have become so complicated that you seem to have to go through several rules and pages to get to the quiz. Could you tell me where I go to play the quiz? I learnt a lot from playing this quiz but now am thinking of stopping because it's taking too long to get to it. Don't get me wrong - I think it's a brilliant idea and so good for keeping on top of PL/SQL changes. I wish the web pages were simpler."
 
We were surprised - and dismayed - to hear this; we didn't think it was hard to play a quiz on the site. If it is, well, that is something we need to fix, and fast.

So we thought we'd ask our players for some feedback on usability of the site. Please take a moment to help us improve your experience at the PL/SQL Challenge.

High Performance PL/SQL Video

On 5 September, Quest Software hosted a webinar by yours truly on Higher Performance PL/SQL. It was just an hour long, so I had to focus very closely on just a few topics: BULK COLLECT, FORALL, Function Result Cache, NOCOPY and - barely squeezed in - pipelined table functions. You can watch the recorded session here . Enjoy!

Roundtable Discussion: Identifier Naming Conventions

Our second Roundtable discussion focuses on an issue that every developer grapples with: how to name our identifiers. That is, what are our naming conventions?

There have been many approaches to creating names, including CamelCase and Hungarian notation.

A longtime PL/SQL Challenge player, John Hall, offers a very different approach:

Identifiers can be considered a form of documentation, which raises the question, "What should an identifier's name document?"I propose this core principle: "Identifier names are based solely on the problem domain concept." No prefixes for scope. No suffixes for type.

In other words, while I would usually write a block of code like this:

DECLARE
   TYPE employees_t IS TABLE OF employees%ROWTYPE;
   l_employees   employees_t;
BEGIN
   SELECT *
     BULK COLLECT INTO l_employees
     FROM employees;
END;

John would, instead, do the following:

DECLARE
   TYPE employee_table IS TABLE OF employees%ROWTYPE;

   all_employees   employee_table;
BEGIN
   SELECT *
     BULK COLLECT INTO all_employees
     FROM employees;
END;

He's already got me thinking differently about how to write my code, though I am not yet ready to force my fingertips into drastically different patterns of typing.

I encourage you to visit the Roundtable, check out the discussion, and add your own thoughts.