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.

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

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 :-) ).

Seeing results from running SQL query (1926)

In the 21 January quiz, I asked you to select the choices that would display "Ellison". Two of the choices were PL/SQL blocks containing calls to DBMS_OUTPUT.PUT_LINE. Two were queries. One block and one query were scored as correct. I received the following objections.

1. "There were () at the end of function invocation. PL/SQL syntax would not allow that."

2."I think it is wrong as you actually ask for code that displays the given text and not which query only runs fine. Therefore only Option 2 is right and not Option 4 as it only selects something but has no attached DBMS_OUTPUT.PUT_LINE statement."

3. "You don't have semicolons at the end of the SQL query. Is it supposed to be correct? It doesn't seem so - that's why I haven't chosen the answer #4 though in general I know it is valid."

My responses:

1. In fact, you can provide "()" after a function call, without any argument values, and Oracle will accept it.

2. When a query is executed it returns data, which you view on your screen - just as you would by running a block with calls to DBMS_OUTPUT.PUT_LINE. One of the queries returned "Ellison" and was scored as correct. I do not believe it should be necessary to provide specific instructions on how to interpret the effect of running an SQL statement.

3. Ah, very interesting! Many developers do  believe that the ";" character is part of the SQL language - it is not. It is simply the default terminating character in SQL*Plus (causing immediate execution of the statement).

In conclusion, I do not believe it is necessary to change the way this quiz was scored.

SF

Cursor FOR loop, automatic optimization and use of BULK COLLECT (1924)

In the 19 January 2011 quiz, you were asked to choose the block containing the most efficient looping through employee data. It was:

BEGIN
   FOR emp_rec IN (SELECT last_name FROM plch_employees)
   LOOP
      DBMS_OUTPUT.put_line (emp_rec.last_name);
   END LOOP;
END;
/

The primary lesson of the quiz is that in Oracle Database 10g and higher, with optimization set to at least level 2 (the default), the compiler optimizes cursor FOR loops so that execute at BULK COLLECT-like levels of performance.

Several players asked about documentation of this optimization feature. Others suggested that a better solution, not offered, is an explicit BULK COLLECT. I have invited them to post their comments here.

Regarding documentation, it looks like in the official Oracle documentation, specific optimizations like this are not detailed out. The feeling seems to be that such optimizations can change over time (to paraphrase: "In version 10 we optimize cursor FOR loops, in version 12 we no longer to do that or do it differently") and so providing a list of "promises" would not be helpful.

Regardless, Ask Tom discusses such optimizations here. I also cover it in my "Best of Oracle PL/SQL" training available here, as well as in Oracle PL/SQL Programming, the book.

So, dear players, please add your comments...

Nuances of Nested Tables (1864)

The 13 January quiz tested your knowledge of nested tables, one of the three types of collections available in PL/SQL (two of which, nested tables and varrays, can be manipulated within SQL).

In response to this quiz, one player wrote: "I am a bit confused about nested tables. As a DBA I know that nested tables are schema objects in the database, but it what we used to call PLSQL tables are now also called nested tables? In my opinion (assuming that I am correct here, which is not necessarily the case of course), this makes today's quiz a bit ambiguous: you can use SQL statements to access nested tables that exist in the database, but not to access PLSQL tables, I believe. It is not clear which type are meant."

To clarify: the datatype formerly known as the "PL/SQL table" is now known as an associative array. It is a PL/SQL-only datatype. That is, you cannot define an associative array type as a schema level type (a.k.a, database object); you cannot use that type as a column in a relational table; you cannot manipulate an associative array with the TABLE operator - all of which you can do with nested tables and varrays. And, interestingly, even if you declare a nested table as a PL/SQL variable, you can manipulate in an SQL statement as long as the type on which it is declared is a schema-level type.

Another player wrote: "Today's quiz about nested tables is not clear. What does it mean "A nested table can have as many elements in it as a relational table has rows"? Example: I'm trying to create a straightforward column with 1001 columns of type NUMBER... it fails. I'm trying to create a straightforward table with 1 column of user type which is not final. The type has many final implementations. Once the total number of virtual columns hit 1000 it fails to insert any new data. I'm trying to create a nested table which has 1001 columns, thus 1001 elements or attributes... wouldn't it fail? Now let's assume that "element" gets redefined into "tuple" or "row". I can insert more than 1000 rows into the nested table. Finally: If I'm a stubborn being then shouldn't be the last answer true as well?"

To which I respond: an element in a collection is analogous to a row in a relational table. Which is to say that you can define and locate an element through its index value. I intended to test with the statement "A nested table can have as many elements in it as a relational table has rows" your awareness that there is an upper limit to the number of elements a nested table may contain (valid index values range from 1 to 2**31-1), while there is no such limit in an Oracle relational table.

Constant Instead of Literal a Best Practice? (1862)

In the 11 January quiz, we asked:

Which of the following statements correctly describe a way to improve the performance, readability or maintainability of this block of code?

BEGIN
   FOR month_index IN 1 .. 12
   LOOP
      UPDATE monthly_sales
         SET pct_of_sales = 100
       WHERE company_id = 10006 
         AND month_number = month_index;
   END LOOP;
END;

We scored as correct the following choice:

"Replace all hard-coded literal values with named constants or function calls."

Several players did not agree. I will offer one such comment and then open it up for discussion:

While it's generally a good idea to replace magic numbers with sensibly named constants, in the case of month names I feel the month number itself is a very well readable and commonly used name (as in "Quiz for 2011-01-11 Tuesday"). And did you know that even in German we have two names for the first month, namely "Januar" and "JÃnner"? But why is this "AND month_number BETWEEN 1 AND 12" clause there anyway? Unless someone invents more months (e. g. "Tricember") all months in any real world table may be assumed to range between 1 and 12 unless they are null. So I would probably change the statement like UPDATE monthly_sales SET pct_of_sales = percent_in WHERE company_id = company_id_in Yet another point is binding: If you introduce constants, they will be bound where they are used in SQL statements. The literals won't. Binding constants is probably not as good an idea as binding variables. Well these are just my 2 cents.

Ah - one other thing: another person objected to scoring this choice as correct because he feels it would WORSEN performance (I will leave it to the player to post his comments here). Even if that were true, the question uses the word "or" not "and" - so as long as replacement of literals with constants satisfies improved readability or maintainability, it does NOT have to improve performance.

So, dear players, what do you think?