Release 1.8 of PL/SQL Challenge in Production

We have successfully upgraded PL/SQL Challenge to release 1.8, which includes these significant new features and changes:

* Remember Me: a long-requested enhancement, you can check the "Remember me" box before you login. We will then automatically log you in to the PL/SQL Challenge on subsequent visits to the website. If you do not visit the site for seven days, you will be prompted to login again.

* Public Profile: you can now record much more information about yourself and your career as a PL/SQL developer. This information is then made available on a public player profile page - but only if you explicitly approve the publication of that content. All player names on the site are hyperlinked to this page. We also provide you with a public URL so that this profile can be accessed from outside of the PL/SQL Challenge website (such as from your blog).

* Streamlined "Take the Quiz" process: You no longer have to scroll down through assumptions and advice. You can choose to view the information or simply press the Play Now button to get right to the quiz.

* Post-Quiz Survey and Player Notes: after you take the quiz, we now invite you to provide us with feedback on the quality of the quiz. You can also record notes regarding that quiz for future reference. The notes will appear on the Past Quiz page. You can also take the survey at any time after you take the quiz.

* Submit Your Own Quiz Idea: you can now submit your own idea for a quiz directly from the website, both from the "Submit Quiz Idea" on the home page and from several other places on the site as well. If your quiz is accepted, your name will be posted the day the quiz is taken. Get creative and share your expertise with us and players from around the world!

* Reorganization of Rules and Assumptions: the Rules page is gone, long live the Rules page! Most of the content of that page is now available in the FAQ. In addition, rules, assumptions and advice for the quiz is provided on the Take the Quiz page.


Warm regards and happy holidays,
Steven Feuerstein

Beta Test of Release 1.8 from 13 December to 17 December

We invite all PL/SQL Challenge players to visit test.plsqlchallenge.com and help us test the beta release of PL/SQL Challenge 1.8. You can log in at this site using your usual email/password combo. The quizzes shown for this week are from the first week of the PL/SQL Challenge. All past quiz data should be available to you for ranking and reporting.

This version of the PL/SQL Challenge includes these significant new features:

* Remember Me: a long-requested enhancement, you can check the "Remember me" box before you login. We will then automatically log you in to the PL/SQL Challenge on subsequent visits to the website. If you do not visit the site for seven days, you will be prompted to login again.

* Public Profile: you can now record much more information about yourself and your career as a PL/SQL developer. This information is then made available on a public player profile page - but only if you explicitly approve the publication of that content. All player names on the site are hyperlinked to this page. We also provide you with a public URL so that this profile can be accessed from outside of the PL/SQL Challenge website (such as from your blog).

* Streamlined "Take the Quiz" process: You no longer have to scroll down through assumptions and advice. You can choose to view the information or simply press the Play Now button to get right to the quiz.

* Post-Quiz Survey and Player Notes: after you take the quiz, we now invite you to provide us with feedback on the quality of the quiz. You can also record notes regarding that quiz for future reference. The notes will appear on the Past Quiz page. You can also take the survey at any time after you take the quiz.

* Submit Your Own Quiz Idea: you can now submit your own idea for a quiz directly from the website, both from the "Submit Quiz Idea" on the home page and from several other places on the site as well. If your quiz is accepted, your name will be posted the day the quiz is taken. Get creative and share your expertise with us and players from around the world!

* Reorganization of Rules and Assumptions: the Rules page is gone, long live the Rules page! Most of the content of that page is now available in the FAQ. In addition, rules, assumptions and advice for the quiz is provided on the Take the Quiz page.

Lots of great stuff, I hope you will agree! Now we need your help this week to uncover bugs and fine tune the flow on the website. If you have a few spare moments, please play around with setting up a comprehensive player profile. Are we missing any critical information you'd like to record about your career as a PL/SQL developer? And take the quiz survey. Are the questions asked appropriate to your experience with the quiz and the kind of feedback you'd like to provide?

Thanks in advance for your assistance and remember: any data entered during the beta test will NOT be carried over to production.

Many thanks in advance,
Steven Feuerstein

Every block will fail with an error? Not so, say players. (1764)

In the 9 December quiz, I test your knowledge of subprogram overloading and the problem of ambiguous overloading. One choice, marked as correct, stated: "Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed." I received two objections to this statement: 1. What if, a player asked, my block looked like this:

BEGIN
   IF 1 = 2
   THEN
      EXECUTE IMMEDIATE 'call salespkg.calc_total(''A'')';
   END IF;
END;

Then this block will not raise an error, even though it "calls" the program. 2. "I do not agree with the answer "Any block of code that includes a call to salespkg.calc_total will result in Oracle raising an error when executed", especially the words "any" and "when executed". Of course, an anonymous block will give a runtime error. But when I include this call in a procedure or package (a "block of code" as well), I will never be able to execute this, because it won't even compile (PLS-00307)! So, in this case, the block of code will NEVER result in an Oracle (runtime) error, because it will never be executed. Because of that, I scored this answer as incorrect" Here is my response: 1. Well, look at that! A player found a "hole" in my statement. I said "includes a call" but I never said that the block had to actually execute the subprogram. Further, he hides the call to the ambiguously overloaded subprogram inside a dynamic PL/SQL block so that the static block with compile. Very ingenious - and irritating. :-) It is so ingenious, in fact, that I am entirely loath to rescore everyone's answers as correct on this point. I will grant that I had a "hole" in my statement and fix that in the text. I will give this player credit for a correct answer on this statement - and I will do so for anyone else who chose "incorrect" for this option because of this - you will need to submit a request through Feedback on the website. Finally, this player (_Nikotin) will receive an O'Reilly Media ebook as a reward for finding a way to maneuver around the language of this statement. 2. I do not agree with this objection. The bottom line is that you cannot execute a block that contains a call to (and that executes) any of these subprograms. Either you cannot run that block because it fails to compile due to an ambiguous overloading or because it is calling a subprogram that is invalid (due to an ambiguous overloading). Either way, you cannot execute that block. Your thoughts?

Questions regarding quiz on object types (7 December quiz) (1762)

A player wrote the following: Hello Steven, Just a few remarks regarding the last quiz, that I would like to hear your opinion on: 1. Regarding the creation on an object type containing STATIC subprograms only, without any attributes: While it is right that such a type is practically substitutable with a PL/SQL package, as far as I know, in Java this is allowed. Since the object oriented features in Oracle usually attempt "to mimic" the constructs and behavior of other object oriented languages, I don't see any reason for such a construct not being allowed in Oracle as well, at least technically, even if practically it is of course of a limited use. The only limitation might be that such a type should be NOT INSTANTIABLE (and therefore not storable in a database column, a.s.o.). In fact, maybe it might contain MEMBER procedures also and still being without attributes. 2. Regarding the restriction that the methods declarations should always come after the type's attributes: I think that this is also a superfluous restriction. While the order of the attributes is essential, just like the order of the columns in a table, for example for clearly determining the order of the arguments in calling the type's constructor, the order of the methods declarations seems to be not relevant and they can probably come as well in any position inside the type definition, similar to the order of subprograms in a package. This would be more similar to Oracle's general behavior of allowing independent clauses in any CREATE statement to come in any order in most cases. As a quick example, in a table definition you can put constraints before column definitions, like in the following: create table mytab (constraint mytab_pk primary key (x), x number not null); It is true that a reasonable person will probably NOT code things like this, and NONE of the examples in the different documentation sources will ever use such an awkward syntax, but this means that you have practically only 2 ways of knowing that this DOES NOT work: a. either to have tried it out by yourself (which probably nobody did, well ... at least until this quiz ... ) or b. to closely follow-up the complete railroad syntax diagram. I am not sure whether this restriction is explicitely mentioned in the documentation. I would love to read your opinion on these issues. I am quite busy at the moment and do not have time to respond, so I thought I would post this for others to consider. Regards, Steven

Misleading terminology in 8 December quiz on varrays? (1763)

The quiz tested your knowlege of the ability to adjust at runtime the maximum number of elements allowed in a varray. One player felt my use of the phrase "upper bound" was ambiguous: 'In todays question we have a problem about term upper bound, because there's a difference between upper bound and maximum size, as pointed in Oracle Documentation as "A varray has a maximum size, which you specify in its type definition. Its index has a fixed lower bound of 1 and an extensible upper bound." (PL/SQL User Guide, Understanding Varrays) So in some choices we can understand which variant of that two is meant, for example in choices … can only be changed… and … using the ALTER TYPE… almost obviously upper bound means maximum size. But in other cases meaning of "upper bound" is not so clear.' To which I reply: an interesting point, but I think that there is no ambiguity in the context of this question, which states: "The varray is one of three types of collections in PL/SQL, and it is the only kind of collection for which you specify an upper bound on the number of elements that can be placed in the collection variable declared on a varray type." In other words, I clearly define "upper bound" as referring to the number of elements. So I do not see that there is any reason to change the scoring of the quiz for this reason. A couple of other players expressed concern about my scoring as correct the statement "You can change the upper bound of a varray from within a PL/SQL block." Here is the most detailed explanation: "I think that one of the choices of today's quiz is a little bit ambiguous, namely the choice that says: "You can change the upper bound of a varray from within a PL/SQL block". The ambiguity is that, if we consider the EXECUTE IMMEDIATE statement, then ANYTHING that can be done at all out of a PL/SQL block can also be done inside a PL/SQL block, be it changing a varray's upper bound or any other DDL operation and even beyond ... The "flavor of the whole question" rather suggests that this WAS NOT the expected way of thinking, otherwise this choice "fails back" to the other one, that speaks about ALTER TYPE. Instead, I think that the intention was more probably to underline that the effective upper bound of a varray variable declared in a PL/SQL block cannot be changed beyond the defined upper bound of the SQL type by using pure PL/SQL statements, like for example l_var.EXTEND, a.s.o. I feel that some different wording of this choice would have been welcome, maybe something like "If L_ARR is a pl/sql variable of a varray type then L_ARR.limit cannot be changed in a PL/SQL block". Am I wrong ? I wonder what others will think of this." To which I reply: watch out for reading too much into the choices. If faced with a choice between trying to deduce the "flavor" of the question and accepting what the choice says "as is", you should always go with the latter approach. I really had in mind nothing more than the fact that you can use EXECUTE IMMEDIATE to also change the limit of the varray type from within a PL/SQL block. Look on the bright side: if you are comfortable enough with Oracle and PL/SQL to know that "anything" can be done in a PL/SQL block with EXECUTE IMMEDIATE, then you can quickly check all such choices as correct in future quizzes! Cheers, Steven

Winners of Q3 Playoffs

On 29 November, we held the long-delayed championship playoff for Q3 2010. A total of 62 players participated. You can view the quizzes for the playoff from the Past Quizzes page.

First of all, congratulations to our top-three ranked players:

1st Place: Niels Hecker of Germany, wins US$1000.
2nd Place: Peter Schmidt of Germany, wins US$500.
3rd Place: Elic of Belarus, wins Complete set of O'Reilly Media Oracle eBooks.

Players ranked 4 through 10 each win their choice an O'Reilly ebook. All players will receive a certificate of participation.

All players rankings are shown below. The information shown in each line is:

Ranking (Weighted Score): Name (country) - # quizzes completed) in (Time) - % correct

You may notice that Niels has a higher weighted score than Peter, even though they have the same % correct and Peter finished in less time. That's because the weighted score is calculated on a per-quiz basis, and is affected by the difficulty of the question.

Again, congratulations to everyone who made it to the playoffs and who play the PL/SQL Challenge with diligence and seriousness.

Warm regards,
Steven Feuerstein

01 (5342): Niels Hecker (Germany) - 10 quizzes in 1091 secs - 81.4%
02 (5324): Peter Schmidt (Germany) - 10 quizzes in 783 secs - 81.4%
03 (5023): Elic (Belarus) - 10 quizzes in 1131 secs - 79.1%
04 (5016): Richard Meyer (United States) - 10 quizzes in 1179 secs - 76.7%
05 (5015): Sean Stuber (United States) - 10 quizzes in 1081 secs - 76.7%
06 (4923): Jeff Kemp (Australia) - 10 quizzes in 1117 secs - 76.7%
07 (4906): Gary Myers (Australia) - 10 quizzes in 732 secs - 74.4%
08 (4901): Randy Gettman (United States) - 10 quizzes in 1181 secs - 74.4%
09 (4879): William Robertson (United Kingdom) - 10 quizzes in 919 secs - 74.4%
10 (4817): Toine van Beckhoven (Netherlands) - 10 quizzes in 1102 secs - 74.4%
11 (4767): Eigminas Dagys (Lithuania) - 10 quizzes in 1180 secs - 72.1%
12 (4737): Frank Schrader (Germany) - 9 quizzes in 1192 secs - 84.2%
13 (4700): Radoslav Golian (Slovakia) - 10 quizzes in 1003 secs - 72.1%
14 (4651): Oleg  Gorskin (Russia) - 10 quizzes in 1121 secs - 74.4%
15 (4588): Rob van Wijk (Netherlands) - 10 quizzes in 938 secs - 72.1%
16 (4539): Dalibor Kovac (Croatia) - 10 quizzes in 1185 secs - 72.1%
17 (4509): Tony Winn (Australia) - 9 quizzes in 1144 secs - 81.6%
18 (4491): emha (Slovakia) - 10 quizzes in 1164 secs - 69.8%
19 (4480): João Barreto (Portugal) - 10 quizzes in 749 secs - 69.8%
20 (4464): Jeroen Rutte (Netherlands) - 9 quizzes in 1186 secs - 78.9%
21 (4458): Jen Croy (United States) - 9 quizzes in 1131 secs - 78.9%
22 (4399): Nopparat Vanichrudee (Thailand) - 9 quizzes in 1008 secs - 76.3%
23 (4375): Michal Cvan (Slovakia) - 9 quizzes in 1060 secs - 73.7%
24 (4329): Alexey Pirogov (Russia) - 10 quizzes in 1173 secs - 69.8%
25 (4324): Riccardo Buttice' (Italy) - 10 quizzes in 961 secs - 67.4%
26 (4316): Justin Cave (United States) - 10 quizzes in 1104 secs - 69.8%
27 (4300): Dennis Klemme (Germany) - 10 quizzes in 910 secs - 69.8%
28 (4266): Filipe Silva (Portugal) - 9 quizzes in 1154 secs - 76.3%
29 (4201): Filip Nikšic (Croatia) - 10 quizzes in 1182 secs - 69.8%
30 (4174): Markus Zuser (Austria) - 8 quizzes in 888 secs - 82.4%
31 (4159): Scott Wesley (Australia) - 9 quizzes in 1099 secs - 76.3%
32 (4117): Robert Marz (Germany) - 10 quizzes in 1151 secs - 67.4%
33 (4038): Xavier Descamps (French Republic) - 10 quizzes in 1160 secs - 65.1%
34 (3923): Kim Berg Hansen (Denmark) - 10 quizzes in 815 secs - 62.8%
35 (3907): Pavel Zeman (Czech Republic) - 10 quizzes in 1151 secs - 65.1%
36 (3905): Davide Gislon (Italy) - 10 quizzes in 1190 secs - 60.5%
37 (3895): pinkal soni (India) - 10 quizzes in 1168 secs - 65.1%
38 (3824): Soumyakanta Das (India) - 10 quizzes in 1128 secs - 62.8%
39 (3823): Hrvoje Torbašinovic (Croatia) - 10 quizzes in 1189 secs - 60.5%
40 (3805): glenm (Australia) - 8 quizzes in 1096 secs - 76.5%
41 (3779): Pietro Toniolo (Italy) - 10 quizzes in 1161 secs - 60.5%
42 (3758): Javid Sch (Azerbaijan) - 10 quizzes in 1083 secs - 62.8%
43 (3750): al0 (Germany) - 7 quizzes in 829 secs - 88.9%
44 (3735): Chris Roderick (Switzerland) - 9 quizzes in 1131 secs - 71.1%
45 (3728): Tim Scott (United Kingdom) - 10 quizzes in 1161 secs - 65.1%
46 (3721): Yuriy Pedan (Ukraine) - 10 quizzes in 1185 secs - 62.8%
47 (3718): Pavel Mitrofanov (Russia) - 9 quizzes in 1199 secs - 73.7%
48 (3664): Piet van Zon (Belgium) - 9 quizzes in 1185 secs - 65.8%
49 (3623): Paul Sharples (United Kingdom) - 10 quizzes in 846 secs - 58.1%
50 (3615): Michael Meyers (United Kingdom) - 8 quizzes in 1133 secs - 79.4%
51 (3612): Sergey Porokh (Australia) - 9 quizzes in 1175 secs - 65.8%
52 (3611): Johan Martensson (Sweden) - 10 quizzes in 594 secs - 60.5%
53 (3601): Michael Haynes (United States) - 10 quizzes in 554 secs - 58.1%
54 (3544): dannyg64 (United States) - 9 quizzes in 1066 secs - 65.8%
55 (3480): Gunjan (India) - 9 quizzes in 958 secs - 65.8%
56 (3371): V Vandana Patel (India) - 10 quizzes in 1167 secs - 58.1%
57 (3365): james su (Canada) - 9 quizzes in 883 secs - 63.2%
58 (3282): Tony Scholefield (Australia) - 9 quizzes in 1167 secs - 63.2%
59 (3269): Christopher Beck (United States) - 8 quizzes in 1130 secs - 67.6%
60 (2795): John Seaman (New Zealand) - 7 quizzes in 1143 secs - 70.4%
61 (2417): Jennifer Schiltz (United States) - 7 quizzes in 1153 secs - 63%
62 (1898): Justis Durkee (United States) - 6 quizzes in 1086 secs - 60.9%

Ambiguities in 1 December quiz on emulating indexes in collections? (1741)

The 1 December quiz tested your knowledge of using associative arrays to emulate multiple indexes into a single collection of data (at least, that was the intention. Several players felt that there were ambiguities in the question. I will provide some of their comments below, but leave it to them and others to elaborate more fully in their responses to the blog. 1. "I thought that the "perform faster than repeated SQL queries" phrase was ambiguous. I would assume I could run several (ie repeated) SQL queries for specific rows in less time than it would take for returning the same rows through the package if we included the initialisation time which is querying the whole table and performing the memory allocation. However, if we took SQL query 2, I would expect the package call 2 to be quicker." 2. "While the 1st answer is simply incorrect (AFAIK, there is no such thing as TYPE ... IS INDEX ON...) and the 2nd is obviously ineffective when doing lookups by partname, there's the 3rd answer I'm unsure about. Its execution time is roughly the same for any number of rows but as for me you cannot determine, if is it faster then plain SQL or not without some actual testing on some real data." 3. "The today quiz is somewhat ambiguous - the problem is with "roughly the same". I do not know implementation details of INDEX BY table but may relatively safely assume that they are map-based (either hash or tree). For this reason their access time should be n*log(n). Not sure if it covered by "roughly"." 4. "There are extra assumptions made, that are not real. In a normal situation I would say that none of the procedures are safe to do. There are indexes on the database. That is fast enough. Don't create code that makes it complex to survive changes to database. Maintenance on systems costs more then the development. Optimizing speed could be done most of time by creating good indexes or partitioning the data. " 5. "the question stated "that the execution times for both functions will be (a) faster than repeated SQL queries of the plch_parts table and (b) roughly the same, regardless of the number of rows in the table?" Your explanation just showed that your solution works but does not *prove* a) or b), is does not even explain it by stating that using a collection is faster than an unidexed query on a relatively big table, especially when (unlimited memory) the whole table might be cached." 6. "I've been enjoying the daily quiz since it began, and this is the first time I've felt the need to question one of the answers! In the 1 December quiz, one of the answers related to looking up a key from one associative array, then using that key into the second array. At the time I agonised over whether the performance would be "roughly the same" as the function that did just one lookup. On most systems the times we are talking about would be small, but for very large arrays, and if the function is called very often, then two lookups instead of one could be significant to response time. So I decided that "twice as long" is not "roughly the same" and did not tick this choice as correct; this was marked as incorrect. Therefore I question whether the specification of this question using "roughly the same" is sufficiently clear to allow us to choose the right answer." OK, let's leave it at that. I will make a few comments and then open it up for discussion. First, it is true that I did not include in the verification code the scripts needed to prove my claims. I will try to find some time to do that over the next couple of days. But if one of you would have the time to do so, and post your code and results, that would be fantastic (either proving my claim or disproving it). Second, yes, certainly using language like "roughly the same" leaves room for interpretation and some ambiguity. But I hope and plan to prove with my performance scripts that it is a reasonable statement to make. Finally, regarding the comment of my assumptions not being "safe," yes, I agree. You cannot assume for your production applications that you have unlimited memory, for example. And I am glad you pointed this out. When crafting quizzes that must by their nature involve the smallest amount of code and complexity as possible (and this question had LOTS of code in it - too much, I fear), I must make some not-real-world assumptions. Cheers, SF