Welcome to PL/SQL Challenge v1.7

We have upgraded the PL/SQL Challenge to version 1.7. You will find below a list of key features and enhancements. I believe you will find the PL/SQL Challenge to be even more addictive and educational than before. I am particularly proud of this version because it marks, in essence, the debut of Eli Feuerstein as a professional programmer. My son, brand new to APEX, PL/SQL, SQL, HTML and Javascript, implemented most of the big UI changes in this release. Congratulations, Eli! And many thanks, of course, to Paul Broughton and John Scott of Sumneva for their continue support.

1.7 Key Features and Enhancements

* Home page: view a random selection of past quizzes that you can visit for a quick PL/SQL refresher, plus recent achievements by players. The home page is, I admit, a bit crowded, but hopefully you will enjoy access to all this information.

* Past Quizzes: Search through all past quizzes by topic, difficulty or date, and then drill down to see a new, more detailed description of the quiz and your results. There is, for example, now a separate section containing the verification code so that you can more easily verify our answers and learn the features more easily. You can also ask to view the quiz without the answers, so you can practice taking the quiz (though you cannot - yet - resubmit answers). Soon you will also be able to see much of the same information in your daily emails reporting on yesterday's quiz.

* Interactive Rankings: built on top of the APEX4 Interactive Reports feature, you can now see rankings by player, country, organization and company (for a given period: daily, weekly, monthly, quarterly, lifetime). You can build your own custom reports. We believe that many of the requests we have received from players for better rankings information will be satisfied by this feature (for example: if you want to keep track of and rank players in the same company, simply choose one of the Rankings by Player reports and then filter by company!).

* Take the Quiz page: Each quiz now includes a "No choices are correct" option at the bottom of the page. You must check at least one of the choices before you can submit your answer.

* Player Profile: you can now tell us the year you started working with PL/SQL and also provide a short description of yourself. We will be adding even more options for describing yourself and what you've done to make yourself a better PL/SQL Developer, all of which will then be made available to other players through a public Player Profile page.

* Assumptions: we've made some minor tweaks to the assumptions, so please review them before you take your next quiz.

* Blog access: you can now visit the PL/SQL Challenge blog by clicking on the new Blog tab button.

I hope you all enjoy the new features. Please let us know what you think. As if I have to tell players of the PL/SQL Challenge that. Many of you, clearly, need no encouragement whatsoever!

While I've got your attention, I'd also like to give you a glimpse of what's coming at the PL/SQL Challenge.

Next up, I will reschedule and hold the Q3 championship playoff. This championship will also, by the way, be the last one with large cash prizes ($1000 first place, $500 second place). In response to suggestions from many players, I plan to reduce the emphasis on prizes and increase the emphasis on learning/education. This change should also reduce the desire of players to violate rules to improve their ranking.

After that, our attention shifts to version 1.8, which will for the first time feature a public player profile page. Names of players will be hyper linked to this page. You will be given the opportunity to tell lots more about yourself, such as what resources and influences helped you become the PL/SQL developer you are; your specializations in the Oracle world; your employment status (which development manager wouldn't want to seek out highly ranked players for their team?). You will, of course, have total control over what is displayed on your public page. In addition, I plan to add polls to the PL/SQL Challenge (just another kind of quiz, really) to the site, so that we can gather more and better guidance from players on how to improve the PL/SQL Challenge.

Looking ahead over several months, I plan to change the way the website works on a more fundamental level. We will move to a points system (kind of a like a "frequent player" program), in which you accumulate points for all sorts of activities on the site, and then redeem those points for prizes of your choice.

After we put that in place, we will offer a membership option for the PL/SQL Challenge, which will provide lots of other great features to those of you who find the Challenge valuable enough to warrant a very reasonable membership fee. These features tentatively include creating and playing as teams, setting up your own competitions (based on previous quizzes or your own content), access to training videos, and more. Of course, the daily quiz and many other features will remain completely free of charge!

Warm regards and best of luck in the coming quizzes,
Steven Feuerstein

Let's talk about varrays and the 22 October quiz (1525)

The 22 October quiz asked "Which of the following statements describe a reason to use a collection of type varray with BULK COLLECT?" The choices and my explanations for them quickly attracted response. I was not terribly surprised. This question is a bit different from most of the previous ones. Rather than ask you to evaluate code, I ask you to pick reasons, which inevitably have to do with intentions and the solving of problems. Good stuff! Or risky material. My newest reviewer (and also, therefore, the most recently active player of my reviewers), Michael Brunstedt, actually pleaded with me to not go with this quiz, precisely because it was "conceptual" and therefore open to (mis)interpretation. "This quiz has bad karma," said Mike. Well, I ignored him and published the quiz. I offer below the feedback of several players. I will hold off responding myself until I hear more from other players. "I'm not sure if the question and answer two (will establish a upper limit...) are really clear. Yes, a varray will establish a upper limit because you will get an exception when more records are fetched than the varray has been defined for. But I'm not sure if that's a reason why I would use varrays instead of other array types. I have checked that question because it establishes that upper limit but in a program it doesn't make much sense because I will get an exception if I don't use the LIMIT clause (which really establishes an upper limit)." "I choose none but I think that you will consider "Use of a varray will establish..." Because you can trap the exception and no nothing and the varray will keep the previous fetched values...even soo that is very bad practice (I think)...you could do that in the SQL (the limit)" "First, I should witness again how much I love the quiz, more and more every day ! Nothing helps, the passion for constant self-improvement is there, it is burning and transforming you not only in a more eager professional, but also in a more passionate competitor !!! Though there are about 2 hours left for today's quiz, I just started to wonder about it having been somewhat different from the usual ones, leaving more place to interpretation. Specifically, I am not completely content with the fact that the second choice: "Use of a varray will establish an upper limit to the number of rows that can be fetched ..." does allow for possible opposite interpretations to this statement. First, strictly speaking, the use of a varray by itself WILL NOT establish an upper limit to the number of rows that can be fetched, except maybe with the following addition: "the number of rows that can be fetched without raising an exception". In fact, the programmer is the one that should take care NOT TO EXCEED the number of the elements predefined in the varray definition, either by safely limiting the number of rows in the result set or by catching an eventual exception raised if the result set is larger than the varray size. I did not check yet whether handling such an exception does indeed ensure that anyway, a number of rows not exceeding the varray size are still deposited into the target varray. If this does indeed happen, then we can be "that generous" and consider the varray as "kind of a tool" that can be used to limit the number of fetched rows, in addition to the other "traditional" ones, like limiting the rows selected or using an explicit cursor with FETCH ... BULK COLLECT INTO ... LIMIT "n". But, again, it is the programmer that uses varray as a limitation tool, and NOT the varray itself. I would say "it is the varray only" if this would happen "silently", without raising any exception, but just ignoring the rows that do not fit into the varray size. By the way, I remember something related to a similar behavior years back, when using pl/sql in SQL*FORMS V3. If a SELECT INTO ... ORDER BY ... happened to return more than one row, then a TOO_MANY_ROWS exception was of course raised, but, if that exception was handled, then the target INTO variables still contained the first row's values (as by the ORDER BY), which was exactly our purpose, so we deliberately used such coding, though it is not the best way to go. When upgrading to OracleForms6i, this behavior changed, in the sense that, after handling the TOO_MANY_ROWS exception, there were NO FETCHED VALUES deposited at all in the target variables, so we changed each such select into an explicit cursor and fetching only one row from it. Second, back to the quiz, considering that the question asked for a "REASON to use varray with BULK COLLECT", the above remark adds even more to the possible ambiguity of this choice: One programmer can consider the varray size limitation and the need to prepare for an eventual exception handling as a rationale AGAINST using varrays with BULK COLLECT, while another programmer, just the opposite, can see in this a possible and "simple enough tool" for obtaining a limited number of rows in a result set, again, as long as the behavior is the one that I tried to describe above and it is not changed by Oracle. Once again, I will test it immediately when I will be back at work and have the database at hand, but, till next week, I just wonder about what other competitors will think about this choice. Regarding the last choice, related to the efficiency of varray versus other collection types, I am not very sure that there is a 100% answer about whether this is right or not, it probably depends also on the number of rows in the result set, though, if I remember it correctly, it is generally considered that when storing varrays IN THE DATABASE (which is not the case here), especially small size ones, they can offer certain advantages regarding storage, ease of manipulation, subscript stability, a.s.o. upon nested tables. So, in summary, if I am allowed a personal opinion, this quiz was much more "phylosophical" than any previous one that I can remember, I dare presume that there will be more comments around it than on some of those previous ones that made a few compeitors so angry about the English text ... Here the controversial case is a "purely PL/SQL" one, so it will be interesting ... " I considered today's quiz questions to be a bit more ambiguous and grammatical than typical, I thought you were trying to eliminate subjectivity and English language skills from these questions . . . I chose not to check the answer "Varrays are the most efficient type of collection, so the BULK COLLECT query will ..." question, because I could not find any Oracle documentation that denoted that 25% improvement is typical, and I know that each configured Oracle environment can behave quite differently than another when it comes to this kind of performance tuning, therefore even though they are the most efficient and faster, the 25% improvement clause causes the statement as a whole to fail the correctness test. I was iffy about checking the "Use of a varray will establish an upper limit..." question, but checked it in the end because someone could indeed decide that their program should raise an exception if more than records are returned, therefore this could be a reason for choosing them since they wouldn't have to wait for the statement to return all the records that will be ignored anyway since there were too many (and also sucking up SGA memory & processor cycles the whole time) until they could check their record count against ." "On the Friday October 22 quiz, I'm not sure that I agree with marking correct the answer "Use of a varray will establish an upper limit on the number of rows that can be fetched with a "SELECT column_name BULK COLLECT INTO array_name ..." statement." I read that as saying that a SELECT ... BULK COLLECT INTO array_name would do the equivalent of an implicit LIMIT size_of_array, which is, of course, incorrect. I would further argue that bulk collecting into a varray does not limit the number of rows fetched-- Oracle is attempting to fetch all the rows. It just happens that the N+1th fetch into a varray of N elements happens to throw an error. " So....what's your opinion on this matter? And now I will prepare for the upgrade to 1.7. Cheers, SF

Questions about "**" from 20 October Quiz (1523)

The 20 October quiz included this line of code:

BETWEEN NVL (start_in, -2**31+1) AND NVL (end_in, 2**31-1)

which drew responses from several players: 1. In 10/20's quiz, the 3rd choice (calling update_parts() with no parameters) causes the "if NULL" logic to activate in the NVL statement of the BETWEEN statement. I have never seen ** notation before; a collegue (who used to program in FORTRAN) said this morning that it indicates raising the preceding number to a power. I have always only seen the carat ^ used for that. When taking the quiz, I quickly tried it out in Toad with: SELECT -2**31+1 from DUAL; to see what it did. But Toad said, "ORA-00936: missing expression". When I searched Oracle's 10g R2 on-line documentation for "**", it comes back with "Your search term ** did not match any topics." So I was hoping it was a typo and left it unchecked. (In fairness, a search on "^" comes back with the same message: "Your search term ^ did not match any topics.") Today, I copied the text of the quiz, ran it all in Toad and the output worked perfectly. So it's not a typo (rats!!). My question is: why does that notation work when used as it is in the procedure, but not straight SQL? My suggestion is: if ** is old-fashioned notation, maybe the quizes can use up-to-date notation? 2. There seemed to be a problem in today's quiz that would make the procedure not compile. You referenced 2 NVL replacement values such as -2**31+1 that seem wrong, surely there is one '*' too many? For this reason I stated that none of the answers were correct. 3. If it wasn't for the double * on the NVLs, I would have chosen the update_parts(-20000,-10000). Was the double * intentional? Well, it certainly was intentional to use "**". I didn't think this would cause very much consternation, but if you've never seen them before, it is understandable that you'd think this was a mistake. It is not. "**" is the exponention operator, as is clearly stated in the Oracle 10g doc here. It is equivalent to using the POWER built-in function.

Questions regarding DELETE from the 19 October quiz (1522)

This quiz explored the use of the DELETE method, which can remove one, multiple or all elements from a collection. It was also one of those rare quizzes in which all choices were correct, at least according to me. One player disagreed. He objected to my scoring on the following choice, in which I specify low and high values for DELETE that are not defined in the collection:

DECLARE
  TYPE numbers_t IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;l_numbers   numbers_t;
BEGIN
  l_numbers (1) := 3;
  l_numbers (2000) := 2;
  l_numbers (-70990) := 1;

  l_numbers.delete (-100000, 100000);

  DBMS_OUTPUT.put_line (l_numbers.COUNT);
END;
/

In other words, I specified the low and high values in the DELETE range to encompass the defined index values. This code does remove all elements, but a player wrote to say: "I'd like to question the validity of the second answer of the quiz for 10/19/2010 - l_numbers.DELETE (-100000, 100000); According to Oracle Documentation - 'DELETE(m,n) deletes all elements whose subscripts are in the range m..n, if both m and n exist and m <= n; otherwise, it does nothing.' Values -100000 and 100000 do not exist hence the DELETE method should do nothing." I have three replies to this: 1. The DELETE method clearly does not match that behavior and never did. Even if the low and high values do not point to defined index values, Oracle will remove elements within that range. 2. This behavior, and not what the player has found in the documentation, is consistent with the SQL DELETE behavior and a BETWEEN in the where clause. 3. I found the following in Oracle documentation:

Deleting Collection Elements (DELETE Method)

This procedure has various forms:

• DELETE with no parameters removes all elements from a collection, setting COUNT to 0.
• DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
• DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

This text says nothing about m and n having to be defined.

PL/SQL Challenge 1.7: beta test and amazing new features

We plan to upgrade the PL/SQL Challenge website to version 1.7 on October 23. This version has many new and wonderful features, including:

* Home page changes: we now show you a random selection of past quizzes that you can visit for a quick PL/SQL refresher, plus a display of players who deserve congratulations for winning an award, high ranking, frequent play, etc.

* Past Quizzes: you can now search through all past quizzes by topic, difficulty or date, and then drill down to see a new, more detailed description of the quiz and your results.

* Interactive Rankings: built on top of the APEX4 Interactive Reports feature, you can now see rankings by player, country, organization and company (for a given period: daily, weekly, monthly, quarterly, lifetime). You can build your own custom reports. We believe that many of the requests we have received from players for better rankings information will be satisfied by this feature (for example: if you want to keep track of and rank players in the same company, simply choose one of the Rankings by Player reports and then filter by company!).

* Take the Quiz page: each quiz now includes a "No choices are correct" option at the bottom of the page. You must check at least one of the choices before you can submit your answer.

Before we upgrade on the 23rd, we'd like to get feedback from you on the features, the data points in the rankings reports, etc. So we have set up http://beta.plsqlchallenge.com for beta testing from October 18 to October 22. Please visit and go exploring. Tell us what you think needs change or improvement.

For the beta test, you can log in with your regular email and password. The quizzes are from a previous week; they are not the same as those on the production site. All past quiz activity should be the same as on the production site.

Warm regards and thanks for your help in testing,
Steven Feuerstein

Known Issues

* Links to PDF documents, zip files and some sponsor images are not working. These will be up and running in the production site.

* Bad formatting on several pages when using Internet Explorer: we are working on these.