Qualified identifiers and error messages - 29 December quiz (1823)

The 29 December quiz tested your knowledge of how you can qualify the names of PL/SQL elements with their scope name (procedure, function, block).

Iudith wrote the following commentary regarding the kinds of errors that are raised across different versions of Oracle:

Regarding the Quiz of 29-dec, Choice 2:

<<plch_employees>>
DECLARE
employee_id plch_employees.employee_id%TYPE;
BEGIN
SELECT plch_employees.employee_id
INTO plch_employees.employee_id
FROM plch_employees
WHERE plch_employees.employee_id = plch_employees.employee_id;
DBMS_OUTPUT.PUT_LINE(plch_employees.employee_id);
END plch_employees;
/

The choice is anyway incorrect, but maybe it is worth to remark that the PL/SQL compiler treats it differently in the different database versions:

1. For Oracle 10.2.0.3.0, the full compiler errors are as follows:

ERROR at line 3:
ORA-06550: line 3, column 17:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 3, column 17:
PL/SQL: Item ignored
ORA-06550: line 6, column 12:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 7, column 7:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 5, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 42:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 10, column 5:
PL/SQL: Statement ignored

2. For Oracle 11.1.0.7.0, the compiler errors are somewhat different:

INTO plch_employees.employee_id
*
ERROR at line 5:
ORA-06550: line 5, column 12:
PLS-00403: expression 'PLCH_EMPLOYEES.EMPLOYEE_ID' cannot be used as an 
INTO-target of a SELECT/FETCH statement
ORA-06550: line 6, column 7:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 42:
PLS-00357: Table,View Or Sequence reference 'PLCH_EMPLOYEES.EMPLOYEE_ID' not allowed in this context
ORA-06550: line 9, column 5:
PL/SQL: Statement ignored

That is, for Oracle11gR1 the PLS-00403 and PLS-00357 errors appear, while in Oracle10gR2 we saw the PLS-00320 error.

So, things change over time, and, in this case, 11g looks more explicit.

Also, the "hiding" of the %TYPE anchoring caused by using a table name as a label can be worked around not only by defining the variable as INTEGER, but also by qualifying the table name with the schema owner in the variable definition, and thus a %TYPE anchoring can still be used.

"Bounds" for associative arrays - correcting an ambiguity - 28 December (239)

The 28 December quiz on associative arrays scored the following as incorrect:

There are no upper or lower bounds on the integer values you can use as index values.

Several players wrote to complain about this scoring, from two angles:

1. "If the array is indexed by binary_integer then there is upper and lower bounds. (-2147483647 .. +2147483647) However if the table is indexed by varchar2, then there are no bounds on the 'integer values'"

2. "There are no upper or lower bounds on the integer values you can use as index values.Actually, there is a limit on the index values, but it is defined by the limit on the BINARY_INTEGER. So I think there is NO actual limit on the index values, just the limit on the BINARY_INTEGER value."

3. One player quoted from my book, Oracle PL/SQL Programming, as follows: ""Unbounded versus bounded A collection is said to be bounded if there are predetermined limits to the possible values for row numbers in that collection. It is unbounded if there are no upper or lower limits on those row numbers. VARRAYs or variable-sized arrays are always bounded; when you define them, you specify the maximum number of rows allowed in that collection (the first row number is always 1). Nested tables and associative arrays are only theoretically bounded. We describe them as unbounded, because from a theoretical standpoint there is no limit to the number of rows you can define in them."

I agree with point 1 (that is, I accept that I was not explicit enough in my phrasing) and disagree with points 2 and 3. My explanations follow:

1. The argument here is that if the associative array is indexed by VARCHAR2, then you can run code like this without any error (provided by one of the players):

SQL>declare
  2     type ty is table of number index by varchar2(100);
  3     tb ty;
  4  begin
  5     tb(2147483648) := 1;
  6  end;
  7  /

while this code fails:

SQL>declare
  2    type ty is table of number index by binary_integer;
  3    tb ty;
  4  begin
  5    tb(2147483648) := 1;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 5

Now, I could argue that if you index by VARCHAR2, then the values used as index values are not integers; they are strings. So I think I could stand firm and insist that this statement is correct, but the bottom line is that from the perspective of a developer taking advantage of this "workaround" she is using "integer values" as the index values.

So I am going to change the wording of this choice to be more explicit, give everyone who select incorrect credit, and rescore.

2. I find this argument to be "hair splitting". The simple fact of the matter is that if an associative array is indexed by BINARY_INTEGER or one of its subtypes, there are upper and lower bounds (minimum and maximum values) that can be used as index values. So what if those bounds are defined, indirectly, through the use of BINARY_INTEGER?

3. I love having my book quoted at me. I conclude two things from this quote: (a) I need to change the wording. Associative arrays are unbounded only from a practical, not theoretical standpoint. It is precisely from a theoretical perspective that they are bounded; and (b) we need to distinguish between the idea of an upper bound on the number of elements in a collection and on the index values of that collection. With associative arrays, there is an upper bound on the integer values that can be used as index values, but there is no practical bound on the number of elements that can be defined in the collection.

Your thoughts as we play the last few quizzes of 2010?

Happy new year,
Steven

So which error is raised in 24 December quiz? (1805)

The 24 December quiz tested your knowledge of what happens when a CASE statement does not contain an ELSE clause and at runtime, none of the WHEN clauses are executed. The answer is that Oracle raises the "ORA-06592: CASE not found while executing CASE statement" error.

Several players wrote, however, to note that the local variable, l_text, is declared as VARCHAR2(20). So this assignment:

the_text := 'Santa is coming (for some, sort of, maybe)';

will raise a VALUE_ERROR exception. In other words, they chose the correct answer, but for the wrong reasons.

It is true that if statement assignment was executed, VALUE_ERROR would be raised and the text "Today is the 24th" would be displayed on the screen.

But that statement is never executed because of the lack of an ELSE clause.

I did not intend to introduce this issue into the quiz; the original quiz submitted by Michael used just "Santa is coming". But I "softened" the text - after all, many do not celebrate, do not believe in Santa, etc. - and neglected to increase the size of the string.

I will correct this in the question text. There will be no re-scoring of quiz answers.

Merry Christmas to those who celebrate,
Steven

Is a CLOB a string? A question raised about the 22 December quiz (1803)

The 22 December quiz tested your knowledge of the capabilities of both EXECUTE IMMEDIATE and DBMS_SQL to parse very long strings. We scored the following statement as incorrect:

"You cannot execute a string of more than 32,767 bytes as a dynamic SQL statement."

Oracle offers various mechanisms, especially in Oracle11g to bypass this limitation (the maximum size, that is, of a VARCHAR2 variable or literal).

One player emailed the following concern:

One of the choices for today's quiz looks somewhat ambiguous, namely the choice that says: "You cannot execute a string of more than 32,767 bytes as a dynamic SQL statement". If we take this literally as it is worded, then we could object that there is no such thing at all as a "string of more than 32,767 bytes", because this is the maximum length allowed to a VARCHAR2 string. If so, then this choice is supposed to be marked as correct. On the other hand, if string means "anything that is character data", including a CLOB, then this choice (though apparently in need to be marked as incorrect) falls over 2 other choices, namely: a) the choice that says "You can use a subprogram of the DBMS_SQL package to parse a SQL statement whose length exceeds 32767 bytes ( by the way, the wording of this choice ellegantly avoids speaking of a "string whose length exceeds 32767, but speaks of a STATEMENT whose lengths exceeds 32767, thus avoiding the above problem). and b) the choice that says "In Oracle11g you can pass a CLOB to EXECUTE IMMEDIATE. These 2 choices in fact do cover in entirety the 2 cases of using a statement contents as a CLOB. I think that the wording of the choice that says "You can use a subprogram of the DBMS_SQL package to parse a SQL statement whose length exceeds 32767 bytes" is excellent, covering (even without specifying it explicitly) ALL the cases by which a statemnent longer than 32767 can be made "to fit into" the requirements of DBMS_SQL, be it by using a CLOB or by "breaking" the statement's contents into elements of a DBMS_SQL.VARCHAR2A array. So, in my opinion, the problematic choice recommends itself to be rescored due to the ambiguous wording.

The SQL Language Reference says the following about CLOBs: "The CLOB data type stores single-byte and multibyte character data." CLOBs are, therefore, character data, which is another general term for string. VARCHAR2s are also character data, with a maximum length of 32767. But I believe that it is correct to interpret string as a general category of data including CHAR, VARCHAR2, NCHAR, NVARCHAR, CLOB and NCLOB (any others?). Others also feel this way; see:  http://www.orafaq.com/forum/t/98334/2/.

You are correct that two other choices covered the correct descriptions of ways to parse very long strings. I do not see, however, what bearing that fact has on the other choice, which is incorrect.

So I do not see the need to rescore. What do you think?

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

29 November quiz: interesting reactions to a "tricky" quiz (1706)

The 29 November quiz, the first authored by Ken Holmslykke, one of my invaluable quiz reviewers elicited several interesting reactions. It was a tricky quiz, no doubt about that. Involving implicit conversions and comparisons of string values, it was a hard one to sort out. I received the following two observations from players: 1. This is not an objection to the quiz at all (I did get i right , this is more a slight concern about "question styles..." Three cheers for Ken (nice guy taking some of the load off Stevens shoulders. But his "question style" is somewhat different. The thing is, that the "topic" of the question mostly is what happens with the implicit conversions of numbers to strings. But the use of all_objects.object_type%TYPE in my opinion mostly serves to confuse the reader so he won't recognize what the question is really about. Had the subtype index_t been declared as VARCHAR2, the question would have been clearer, that the subject was to test the readers knowledge of how a string associative array works if you use numbers working with that array. So just a slight warning (mostly to Ken) not to try to be too much "clever" when formulating questions. PL/SQL Challenge is mostly about learning, which to me means that it should be reasonably clear what topic the questions are trying to test our knowledge about. This tricky "obscuring" of the question topic is not (IMHO) consistent with the normal "question style" of Steven. That said, I do believe it will in the long run be a good thing to have questions from different authors, who might have a different angle than Steven Just my 2 bits. 2. It's a first time when I really want to nominate this question as a "Question of Year" (or at least a Quarter). I choose a wrong option, but I don't regret. It took a while for me to explain this right option. The only concern I have is about NLS_SORT / NLS_COMP parameters. People will definitely raise this point up, because you even may create your own character set definition and specify a different sort order using those parameters. I hope it's hard to achieve it and and question will be left unmodified. Regarding #1, ironically, though Ken wrote the original quiz, I did some editing and I am the one who introduced the use of the subtype. Ken originally declared the type as VARCHAR2. So I must take the "blame" for that obfuscation. Regarding #2, we document in the assumptions a default installation of an Oracle instance with a specific character set. Is that not enough to cover the issue raised regarding NLS_SORT and NLS_COMP? We'd love to hear what you thought of the quiz.

Indeterminate behavior with same variable bound to multiple placeholders? (26 November quiz) (1710)

Several players did not agree with the explanation provided for one of the choices of the 26 November quiz. This quiz tested your knowledge of the need to specify an OUT or IN OUT mode for bind variables when retrieving values of PL/SQL expressions from a dynamic PL/SQL block. We showed this code as a choice:

DECLARE
  l_best_friend   VARCHAR2 (100);
BEGIN
  EXECUTE IMMEDIATE 'BEGIN special_friend ( :n, :v1, :v2 ); END;'
     USING 2010, OUT l_best_friend, OUT l_best_friend;

  DBMS_OUTPUT.put_line (l_best_friend);
END; 

We scored the choice as incorrect, explaining: "This block will run without error, but it will also display the name of the lobbying firm, rather than the company name. That's because I used the same variable for the two OUT bind variables." Three players argued that while the choice is truly not correct, the explanation is wrong, because as one person put it "This is an example of parameter aliasing. The result is INDETERMINATE. Any correctness of this choice is questionable, because it can not be proven by documentation." Rather than comment further, I offer this post as a starting point for discussion, especially from those who communicated their concerns to me directly. So please post your comments and code samples for all to learn from! SF

Questions raised about 24 November quiz and program invalidations (1709)

The 24 November quiz asked you to "describe a benefit of using the function implementation [to 'hide' a literal value] rather than declaring a constant in the package specification." We scored the following statement as correct: "The function implementation reduces the likelihood that program units will be invalidated thus requiring fewer recompilations of the application code base." A few players wrote with the following concerns: 1. "I think the correctness of option 2 (The function implementation reduces the likelihood that program units will be invalidated thus requiring fewer recompilations of the application code base.) for Nov 24th, 2010 quiz is arguable. The business rules are changing in time so often, and those changes may result in (in our case): i. salary change ii. abortion of the minimum salary policy that may force us to erase both the constant and the function. iii. salary data type change iv. minimum salary calculation change, which may cause alterations on the interface of minum_salary function etc. Implying that "the salary change amongst the statements above is much more likely to happen" is not true. According to the billions of conditions of the business, one of the other options may happen more frequently for a particular period and those options may lead us altering the package specification. Thank you." 2. "Do you think that the answer "..the function reduce invalidation..." for the Quiz is 100% true and doesn't have any side effect? I understand that recompilation of only package body is better for other units, but for the existing session is this recompilation bad too. Session lost the package body context (lost value of body variables), isn't it?" 3. "I assume that today quiz's answer " .. function implementation reduces likelihood ..." meant "... when changing the value of the constant". However I missed it in the text. It seemed a rather vague answer :(" Point #1 makes some very interesting observations about the dynamic nature of our applications, but I don't think this perspective changes the fact that by hiding the literal value in the package body, you can avoid invalidations and therefore recompilations when the value changes. Point #2: sure, when you recompile the package body, this can have some side effects (most notably the dreaded ORA-04608 error that occurs when package "state" is altered), but I again I don't think this point argues against the correctness of the choice. Point #3: yes, adding the phrase "when the literal value changes" would make the choice more explicit (or at least give you more of a hint regarding whether or not it is correct), but as a general statement, I still feel it is valid as is. SF

PL/SQL Challenge down for 3.5 hours, quiz results for 24 November are valid

The rules for the PL/SQL Challenge state the following:

When Scores and Rankings May Be Discarded

If the PL/SQL Challenge is unavailable for more than four hours on any given day, all answers for that day will be ignored. They will not play a role in the ranking. That way, anyone who lives in a time zone for which the downtime made it difficult to answer the quiz will not be penalized.

The PL/SQL Challenge site was unavailable for the last 3.5 hours of 24 November. Up to that time, 1149 players took the quiz. This means that it is likely that several hundred players missed the opportunity to play.

It is very unfortunate that this happened, but the rules and the rules; the results for 24 November will be counted in rankings.

Of course, I very much hope and expect that this sort of thing will be a very rare occurrence. Having said that, it probably wouldn't hurt to get into the habit of setting aside two times during your day when you try to take the quiz. That way you have a "backup."

PL/SQL Challenge server down at 21:00 UTC

The PL/SQL Challenge server is experiencing technical difficulties, making it impossible to play the daily quiz.

We hope to have it back up soon, so if you have not yet played, please check again later (there is about 2.25 hours left for the quiz today).

Over 1130 people did play before the site went down.

Our policy is to void the results if the site is unavailable for 4 or more hours. At this point, I expect to use the results for today, but will make a final determination when the day is done.

Our apologies,
Steven

Players don't like "execution section" in 19 November quiz (1663)

In the 19 November quiz regarding RETURN statements, many players objected to our scoring as incorrect the following statement: "A RETURN statement can only be executed from within an execution section." We said this is incorrect because you can also have a RETURN statement in the exception section of a block. There were two objections to this scoring: 1. The term "execution section" is not found in the Oracle documentation. Instead, the term "executable part" is used. Thus, the question was ambiguous and should be re-scored. Here is a "typical" comment from a player on this point: "The November 19 quiz used the phrase "execution section." A search of the 10gR2 documentation on the OTN site for that phrase does not return any hits, which indicates that "execution section" is not a defined Oracle concept. The phrase "execution section" is ambiguous since it lacks a definition that would preclude one from inferring an intent to separate code into executable (i.e., procedural) and non-executable (i.e., declarative) portions." 2. The "executable part" of a block includes the "exception handling part" of a block, since Oracle documentation states in Understanding Block Structure: "A PL/SQL block has three basic parts: a declarative part (DECLARE), an executable part (BEGIN .. END), and an exception-handling (EXCEPTION) part that handles error conditions." Several players concluded that since the executable part goes through the END; statement, it includes the exception section. And so this choice should have been scored as correct. I will address these separately. First: "Execution Section" is not a defined term I was very surprised to see this response. Yes, these players are correct. In the documentation, the only part of the block that is required is referred to as the "executable part." It is, however, very hard for me to accept that you would not read the term "execution section" and see that it refers to the very same part of the block. After all, if I said to you "look at the statements in the declaration section," I do not think you would say "What's that? I only know about the 'declarative part.'" (which is how Oracle, in this same part of the documentation refers to the, um, declaration section. It I talked to you about the "exception section" would you interrupt me with a correction: "Sorry, Steven, there is no such thing. There is, however, something called the 'exception-handling part.'?" I do not believe that using the word "section" instead of "part" should cause a problem in understanding. I also do not see how using "execution" instead of "executable" would lead to confusion. Furthermore, a quick search on the Internet for "PL/SQL execution section" shows several hits, all showing references to this "executable part" as the "execution section." My impression is that those who objected to the term "execution section" were wrestling with the question of whether or not it is acceptable to consider the exception section to be part of the execution section. So then you looked for a definition of the term "execution section" in the Oracle documentation, could not find it, and then raised an objection about that. Exception Section part of Execution Section? In the Block Declaration section of the 10g PL/SQL Users Guide and Reference, we find: "The basic program unit in PL/SQL is the block. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords partition the block into a declarative part, an executable part, and an exception-handling part.." The definition of a procedure states: "The procedure body has three parts: an optional declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the procedure. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution." In the most completely elaborated description I could find (About Subprogram Structure of the 11g 2 day Developer's Guide), you will see the following: The structure of a procedure is:

PROCEDURE name [ ( parameter_list ) ]
 { IS | AS }
   [ declarative_part ]
 BEGIN  -- executable part begins
   statement; [ statement; ]...
 [ EXCEPTION -- executable part ends, exception-handling part begins]
   exception_handler; [ exception_handler; ]... ]
 END; /* exception-handling part ends if it exists;
         otherwise, executable part ends */

I suppose you could argue that it is unfair to look at 11g documentation, but this question has to do with establishing a reasonable understanding of terminology and use. It seems pretty clear to me that Oracle's intention is that the exception section (the "exception-handling part") is considered separate from the execution section (the "executable part"). Yes, some parts of the documentation mention that the execution section is between BEGIN and END, but in all the paragraphs where that appears, Oracle also clearly distinguishes between the "executable part" and the "exception-handling part." Clearly, Oracle considers these to be distinct parts or sections of the block. As noted above, I also believe it is entirely reasonable to expect a PL/SQL developer to "equate" the following terms:

• "declaratitive part" and "declaration section"
• "executable part" and "execution section:
• "exception-handling part" and "exception section"

So I do not believe there is any need to re-score this quiz. I can understand the frustration you experience with both my quizzes and Oracle documentation. I have never before realized just how "fuzzy" the "logic" is in the documentation when it comes to clearly defining terms. Partly and precisely because of that, however, we have to rely on reasonable interpretations of phrases. I believe in the case of this quiz, my terminology should be acceptable and understood clearly enough. OK, players, time to blast away. SF

Time to change the default DB version to 11.2?

Since the start of the PL/SQL Challenge in April 2010 (my, that seems like a long time ago now), the default database version for the quizzes has been Oracle Database 10g Release 2.

This means that, unless otherwise mentioned, the quiz would not address features of the Oracle database added in 11.1 or 11.2.

I received the following question from a player yesterday:

"For Oracle Database release 10.2 Premier Support has ended some time ago ( well, July 2010). I can't find any announcement on when you are planning to modify your Daily Quiz assumptions to shift to Oracle 11g ? Or why are you not planning to?"

The main reason for using 10.2 as the default version is that, so far as I can tell, it remains the most common version on which PL/SQL developers are working. To require them to be aware of features from 11 could increase the amount of time spent on the quiz, making it less attractive to players.

Having said that, Oracle Database 11g has been out for a while and it is the current production release, so there is every reason for PL/SQL developers to learn the features of this version.

Any thoughts from other players on this topic?

Cheers, SF

Typo in 17 November quiz requires rescoring for 24 players (1681)

The question for 17 November asked you to answer questions about "a nested table" when it should have said "a nested table type." This mistake, which resulted in all choices being incorrect, was fixed early in the day. A total of 24 players had, up to that time, chosen "all wrong" for their answer. They will receive full credit for their answer and their choices will be changed to correspond to "fixed" correct answers. For anyone else who answered "all wrong," well, you don't have their excuse (and sharp eyes) for a justification. Cheers, SF

What are the Datatypes of TRIM arguments (16 November quiz)? (1647)

The 16 November quiz tested your knowledge of the TRIM function. In the explanation of one choice, I wrote: "Both trim character and original string can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype if the "source string" (against which trimming will be performed) is a character datatype and a LOB if the source string is a LOB datatype. The return string is in the same character set as the source string." This is, to be honest, mostly copied from Oracle documentation. One player, Tony, wrote with the following perspective: "I suggest that this is incorrect. The example in the documentation immediately below that [the description of argument types] shows trim(leading number from date) - yes it performs an implicit to_char() around both, but the arguments themselves are of type NUMBER and DATE." Here's my view on this: the datatypes of the arguments or parameters of TRIM are defined by that function's header. It's true that with implicit conversion you can provide a number in your call to TRIM, and Oracle will convert it into a string. Regardless, by the time that TRIM is actually invoked, it is being passed a string, not a number. So number or date cannot be classified as datatypes of TRIM parameters. Your thoughts? SF

Watch out for impact of NLS_SORT and NLS_COMP settings on string comparisons (1645)

The 12 November quiz asked you to evaluate various implementations of ways to find matches on strings in a collection. Peter wrote with the following observation: "This is actually not a question about a quiz, nor an error/ambiguity report; just my intention to point out an environment-dependent behaviour in one of the quiz answers. It's about the 12 November's quiz and the answer choice using associative array copied from the original nested-table collection. Well, the thing is, the output of the given anonymous PL/SQL block is dependent on the setting of two parameters - NLS_COMP and NLS_SORT. Given the standard setting of NLS_COMP = BINARY, the block would yield the TRUE, TRUE, FALSE as requested in the quiz question. "However, if NLS_COMP is set to LINGUISTIC and NLS_SORT is set to e.g. BINARY_CI, the block would output TRUE, TRUE, TRUE, as the result of 'Steven' being case-insensitively equal to 'steven'. This behaviour is well described in Oracle DB documentation. I search the "Rules" about Oracle environment assumptions and found no mention about these specific settings. So I just would like to point it out; I don't really take this to be a quiz error. "The second (for me *the* more interesting thing about this quiz) is the behaviour of the MEMBER OF operator used in one of the other answer choices. I would think that this operator, as it's (I believe) merely comparing the array values against a single value, would be prone to produce different results depending on NLS_COMP/NLS_SORT parameters too. Yet, it is not. I cannot explain this, I merely point out again as an interesting fact. All my tests were done on a 11.2.0.1 version of Oracle EE DB. I don't have a 10g R2 to play with so cannot tell if the MEMBER OF thing is anyhow 11g specific." Many thanks, Peter! My sense is that our assumptions (default installation of Oracle) means that we do not have to add another assumption. But this is fascinating and useful information about which we should all be aware. Cheers, SF

Numbers are names, too! A mistake in the 11 November quiz (1644)

The 11 November quiz contained a mistake, due mostly to incomplete "specifications" in the question text. The question stated that I created a table like this:

CREATE TABLE plch_employees (
  employee_id INTEGER,
  last_name VARCHAR2(100)
)

and then ran a block at 2 AM each morning that worked with data from this table. I scored as correct the following choice: "Oracle will raise a NO_DATA_FOUND exception if all the employee last names have no more than 10 characters." and my explanation for this choice stated: "If I manage to populate the collection with rows from my table, then Oracle will certainly raise NO_DATA_FOUND when it tries to execute the numeric FOR loop. This is so because the index values are strings and not integers, so there is no possible way that the collection is sequentially filled from index value 1. As a result, this code will raise a NO_DATA_FOUND exception - unless the employees table is empty." Well....many, MANY players had a field day with this choice. They were especially entertained by my "no possible way" phrase. You see, I never stated any rules about an employee name. Consequently, the names in the table could all be integers (1, 2, 3, etc.) and in this case, the block would not raise NO_DATA_FOUND. And, even more sadly, this mistake affected TWO of the choices for the very same reason. The choice "I will see the employee IDs for every row in the employees table." could be true if all employee last names were integer values starting with "1" and proceeding sequentially to the number of employees in the table (1,2,3...). Silly me - and very sharp of so many players for noticing this gap in my specifications and therefore the possible behaviors of my code. I am, once again, impressed at the close attention paid by so many PL/SQL Challenge players to these quizzes. Assuming you pay the same amount of attention to your application code, I am certain those applications are of the highest quality! I will give everyone credit for both these choices, and change the question text so that it is clear that names contain characters. From all those who notified me of my mistake, Oleg Gorskin was selected randomly to win an O'Reilly Media ebook. Congrats, Oleg! Any other comments/objections to this quiz? Cheers, SF

Questions regarding NEW and OLD quiz of 10 November (1643)

The 10 November quiz asked: "Which of the following statements correctly describe how you can reference the new and old column values of the current row affected by the triggering statement of a row-level database trigger?" Several players wrote to us with the following concerns: 1. We scored the following as incorrect, because if you use a WHEN clause for your trigger, any references to NEW and OLD within this part of the trigger cannot have a ":" prefix. "A colon (:) must precede the OLD and NEW qualifiers wherever you reference the old and new column values in the code to define a row-level trigger." Several players objected to this, stating the "the code" to define a trigger only refers to the part of the trigger that starts with the DECLARE or BEGIN section of the trigger definition. I do not agree. I think that "the code to define a trigger" refers reasonably to all the statements that you write to define the trigger. Another person objected to this scoring as follows: "But in the WHEN clause you are not referencing "column values" as you write in the answer. You are just merely defining the keywords. So "wherever you reference the old and new column values" you do need to specify a colon. And therefore I feel that this answer is correct." How is it that I am not referencing column values when I write:

WHEN NEW.employee_id = OLD.employee_id 

This interpretation of "code" to not include parts of the CREATE TRIGGER statement and the statement that I reference only the "keywords" and not the values seem to me to be parsing words a bit too closely; some measure of reasonable interpretation is required and these objections both fail on that count. I do not feel that any re-scoring is required for these objections, though I may consider making some changes to the quiz text to the possibility of mis-interpretation in the future. 2. A player wrote: "There is a choice in the today quiz starting with "The trigger fired by the DELETE statement ..." - it does not state that the trigger is a row-level one, so I did not mark it as valid. Is it not mentioned intentionally? BTW, the similar statement regarding triggers fired by INSERT explicitly states the the trigger is row-level one." To which I reply: If the choice that you point out was a "stand alone" statement, then your concern is valid and you would be right to mark it as incorrect. It is, however, a choice for the following question: "Which of the following statements correctly describe how you can reference the new and old column values of the current row affected by the triggering statement of a row-level database trigger? " so clearly the context is "row-level" and I do not believe that any change is needed to the scoring. I will, however, add "row-level" to that choice so that in the future others do not experience the same problem. Cheers, Steven Feuerstein

Ambgiuous reference to "storage clause" in 8 November quiz (1641)

In the November 8 quiz, we scored the following statement as correct: "When you use a nested table type as a column datatype, you must also include a storage clause for that column." P.H. wrote to say that he disagreed, as he could create a nested table as follows:

create table t_test_nt_3
 (
          col1    arr_numbers,
          col2    arr_numbers
 )
 nested table col1 store as t_test_nt_3$col1
    storage (initial 1m next 1m),
 nested table col2 store as t_test_nt_3$col2;

Well, yes, you can do that. But what I meant by "storage clause" was the entire STORE AS...[STORAGE ...] clause. I can certainly see, however, why this term was not clear enough, and left the quiz (and me) vulnerable to a charge of ambiguity- unless Oracle itself was very clear on this topic in its documentation. Unfortunately, it is not. I found two references to "storage clause" as the entire STORE AS ... STORAGE part of the column declaration: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjcol.htm#sthref461 http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjdes.htm#sthref834 These pages support my use of the term "storage clause" and my scoring of the choice as correct. This link, http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjcol.htm#sthref450, however, states the following: "Elements of a nested table are actually stored in a separate storage table.... Oracle stores nested table data in a single storage table associated with the object table for both nested table types that are columns in a relational table or attributes in an object table. The storage table contains a column that identifies the parent table row or object that each element of the nested table belongs to....The NESTED TABLE..STORE AS clause specifies storage names for nested tables. Storage names are used to create an index on a nested table." If this last page had definitively referred to the whole "NESTED TABLE...STORE AS" as the "storage clause," I would reject P.H.'s claim of ambiguity. But I cannot do this. So I will give everyone credit for a correct answer on this choice, and change the text to make it unambiguous. I am currently in Dallas for the Oracle PL/SQL Programming/APEXposed conferences, so I may not update scores and ranks until I get back home on Friday. Congratulations to P.H., the only player who noticed this ambiguity and who wins an O'Reilly Media ebook.

You feedback needed: how best to show "you got right/wrong" info for past quiz?

In response to player requests, in the 1.7 upgrade of the PL/SQL Challenge, we now display for each quiz choice detailed information about how you did compared to other players.

The text will look like this:

You got it right
76% got it wrong.

or

You got it wrong.
42% got it right.

In other words, we display the % of people who answered differently from you. I thought this would be a nice way to highlight your relative performance.

One player, at least, does not like this, writing: "While I was going through the results, I noticed that the statistics on how well the others are doing (23% of all players got it ....) sometimes says 'wrong' and sometimes 'right'. It would be more consequent if they always say 'right' (or 'wrong' if you prefer that option). Now I must do calculations in my head (arghhh...) after reading the text and interpret what was the sentence to compare how well the others did on the answer and to see how stupid I was in selecting the wrong answer compared to the others."

Does anyone else have any strong (or otherwise) opinions about this?

Answer to 5 November Quiz is Wrong (1621)

The 5 November quiz tested your knowledge of the way that the value of the SQL%ROWCOUNT attribute is set. Vitaliy wrote to me that while the quiz was scored correctly, the explanation of the results are wrong: 1. Until a SQL data manipulation statement is executed, SQL%ROWCOUNT yields NULL. Not zero. 2. SQL%ROWCOUNT=0 in your code because the COMMIT changes the SQL% attributes (reset rowcount to 0). (JFYI, in 7.3 commit does not reset SQL%ROWCOUNT to 0 and when upgrading some people had trouble with it). Vitaliy then points us to the Oracle documentation: "The value of the SQL%ROWCOUNT attribute refers to the most recently executed SQL statement from PL/SQL....The SQL%ROWCOUNT attribute is not related to the state of a transaction. ...Also, when an autonomous transaction is exited, SQL%ROWCOUNT is not restore to the original value in the parent transaction." I will change the text of the answer so that it is accurate. My apologies for this - but of course happy as always to be corrected! SF

The quote character (q) and the 4 November quiz (1604)

This quiz tested your knowledge of the new-to-10g feature of using the q character so that instead of doubling each single quote inside a string literal, you can specify your own delimiter character for the literal, and then use single quotes inside the string, thereby making it easier to read and manage dynamically constructed strings. I received several emails with highly varied responses to this topic, some indicating what I can only consider are bugs in this feature or in the way they were used when players tested the feature. I have had no problems with using the q character on both Oracle10g and Oracle11g databases. 1. "I loved today's question, I was not aware of the q function...and I could immediately use it today, so another victory for the Challenge!" 2. "Something strange happens related to this quiz, regardless of the choices presented. It looks like, in general, the alternative quoting mechanism does not work at all if the literal string contains a SINGLE QUOTE character as the first character of the literal value. This restriction IS NOT documented in the Oracle SQL Language reference, however it gives the following error: SQL>select q'*'Hello,' said the man, who didn't like goodbyes.*' 2 from dual; ERROR: ORA-01756: quoted string not properly terminated though, as per the documentation, this was supposed to work. Removing the first quote character and leaving all the alternative delimiter stuff in place makes things working. The case is the same for both SQL and PL/SQL statements. The documentation only says that: "...if the quote_delimiter appears in the text literal itself, ensure that it is not immediately followed by a single quotation mark." which IS NOT our case. This practically renders ALL the choices to this quiz as incorrect, except for the one that uses the classical quote delimiter, though I am sure that this WAS NOT your intention, you probably intended to check the knowledge about the "alternative quoting" feature, and not to make all of us stumble upon this documentation bug ... I dare say that: 1. Most of the players were not aware of this restriction regarding the first character in the literal. 2. It could be interesting to know what percent of people are using this mechanism at all in their daily work ... I am sure that many other players will comment on this quiz." 3. From Christian Rokitta: "I just published a blogpost on today's quiz subject" 4. "Thanks for bringing this topic up. I did not know that Oracle had introduced this ability. (Thus I got a very poor score in yesterday's quiz) I tried it out immediately - but your examples did not work on my 11g database (I know, the quiz assumes a 10g database, but features introduced in 10g should also be available an work in 11g also). Playing a bit around I found out that the user-defined delimiters works when there are a even number of quote signs in the string literal, but not when the number of quote signs is uneven, as in your examples. I wonder, is this a bug in 11g?" 5. "This is a new one for me, the Q mechanism. Had to quickly search for the documentation and made one mistake, the () I oversighted. But is the sentence correctly English? The comma should not be there?" Regarding #5: Hmmm, you know, I think you are right. I am used to putting a comma before and after the speaker of a quotation, but perhaps in this case the "who" should flow directly after the "man". I will check with my wife, who has a degree in English literature. I am puzzled by the reports of this functionality not working. Again, I did not experience any problems. Perhaps you could use the verification code on the Past Quizzes page and see if, indeed, my precise code in the choices are causing you errors. Let us all know what you find out.

On Winners and Adjustments for the end of October

You will find on the PL/SQL Challenge a list of the most recent winners. I ran into an interesting and new circumstance for October that I wanted to highlight for you.

At the end of each month I account a winner of the "most correct" raffle. You are eligible to participate in this raffle if your correctness over the month was at least as high as the #1 ranked player -regardless of the amount of time it took you to answer the quizzes.

When I ran my script to pick this "most correct" winner for October, my code raised a NO_DATA_FOUND exception. After a quick debug session, I discovered the reason:

No other players matched the 100% correctness ranking of Eurico Matos in October 2010!

Achieving 100% correctness for an entire month is quite an achievement in general, and even more so when no other players were able to do so. Congratulations, Eurico!

I ran the adjustment algorithm for very fast answer times this weekend as well, so some of you might notice a sudden downward shift in your rankings. Remember: take your time, read carefully and thoroughly.

Cheers,
Steven Feuerstein

A better alternative to NVL2 (29 October quiz) (1565)

The 29 October quiz tested your awareness of the NVL2 function (which works quite differently from NVL), and the fact that it is available only in SQL, not yet in the PL/SQL language. The quiz offered several options that served, in effect, as alternatives to NVL2, from within PL/SQL. Vitaliy wrote to suggest that I had missed a simpler and more elegant alternative: CASE. So just in case you would like to use it....

CREATE OR REPLACE FUNCTION NVL2 (val         IN VARCHAR2
                              , ifnotnull   IN VARCHAR2
                              , ifnull      IN VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  RETURN CASE WHEN val IS NOT NULL THEN ifnotnull ELSE ifnull END;
END;
/

26 October quiz: objections to marking a choice as incorrect (1583)

We are about to kick off the OPP/APEXposed conference in Brussels, so I will post this note to get the discussion going, and going in a very lively manner. Then I will come back to it later. In this quiz on read-only transactions, I marked the following choice as incorrect: "The SET TRANSACTION READ ONLY statement can appear anywhere in your PL/SQL block, regardless of the location of SQL statements in that block." Here is one of the objections:

This answer should be marked "correct" : it says nothing about what will or won't happen when the code is *executed*. It only says "can appear anywhere in your PL/SQL block", which I take to mean "the PL/SQL block will compile without error regardless of where (in the executable section) the statement appears".

I will ask the others to post their concerns on this blog. Then I will offer my response later. Cheers, SF

Questions about SQL%ROWCOUNT and FORALL from 20 October Quiz (1523)

Whoops! I overlooked (or, to be more honest, was too busy to respond to) a couple of submissions by players regarding this quiz that tested your knowledge of using the INDICES OF clause with FORALL. Here are the questions/comments: * "When the update statement is not executed, sql%rowcount is not 0, but it is NULL. dbms_output does not write out 0, but a blank space." * "Can you please give more information on why SQL%ROWCOUNT is null returned as null when we use the forall construct with no elements referenced? I am wondering if there will ever a case where a null is returned because the update is never fired." In fact, it is true: if no SQL statement is executed, then SQL%ROWCOUNT returns NULL, as I verified by running the following block (see the Verification Code for this quiz on the Past Quizzes page for the create table statement for parts, and so on).

DECLARE
  TYPE part_aat IS TABLE OF parts.partnum%TYPE
                      INDEX BY PLS_INTEGER;

  l_parts   part_aat;
BEGIN
  FORALL l_index IN 1 .. 0
     UPDATE parts
        SET partname = UPPER (partname)
      WHERE partnum = l_parts (l_index);

  DBMS_OUTPUT.put_line ('Rows modified = ' || SQL%ROWCOUNT);
END;

Was I, then, wrong in scoring the following choice as correct (with the associated explanation)?

BEGIN
  update_parts (100000, 10000000);
END;

"And the output displayed is: Rows modified = 0. There are no elements defined in l_part_indices between 100000 and 1000000. As a result, none of the index values in l_parts will be used, no elements of the l_parts array will be bound into the DML statement, and no rows will be updated."

Hmmm. A very good question. SQL%ROWCOUNT definitely returns 1, but I cannot explain it to my satisfaction. It looks as if, with the INDICES OF and BETWEEN clauses, the FORALL gets "far enough" to be considered by the SQL engine to have run something, but having not actually changed any rows returns 0 for that attribute. Anyone out there have a better explanation? Cheers, SF

25 October Quiz Requires Correction (1561)

First, my apologies for the lateness of this correction. I flew from Chicago to Brussels last night for the OPP/APEXposed conference. Just catching up with all things PL/SQL Challenge now (13:30 Tuesday). So: this quiz tested your (our) knowledge of how the ASCII function handles strings with more than one character. Namely: it ignores all characters after the first and "does its thing" with just that first character. I therefore scored as correct the following choice: "The ASCII code of just the first character is returned." I even wrote some code to test it. My reviewers checked it over. We all liked it. But one player, Ryan, objected. He claimed the choice is not correct, because: "The quiz assumptions make it clear that you are NOT using ASCII, since they state that 'the database character set is an 8-bit character set.' ASCII is a 7-bit character set. It is sometimes encoded as 8-bit, but even then the character set itself is still 7-bit. You cannot guarantee you'll get the ASCII value out of the function when the database is using an 8-bit character set, as the first character of the supplied string could easily be a character which does not have an ASCII representation. If you added an assumption to the question that either that the supplied string either does not start with (almost gives away the correct answer) or does not contain any non-ASCII characters, then you could get away with the answer as written. Or you could just state that the database was using a 7-bit ASCII character set." Ah, Ryan, you are so right. And I find myself now saying: "If only I had checked the Oracle documentation first." Because it describes the ASCII function as follows: "ASCII returns the decimal representation in the database character set of the first character of char. char can be of datatype CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The value returned is of datatype NUMBER. If your database character set is 7-bit ASCII, then this function returns an ASCII value. If your database character set is EBCDIC Code, then this function returns an EBCDIC value. There is no corresponding EBCDIC character function." In other words, if I had only phrased my choice as: "The decimal representation of just the first character is returned." Then everything would have been fine. That was careless of me (well, and I must admit, ignorant). I have now added another item to my checklist for reviewing quizzes: "Check the Oracle documentation. It may not be 100% correct, but it certainly is an important place to start." In addition to all that, one of my reviewers noted that our assumption states "the database character set is an 8-bit character set; the national character set is AL16UTF16." But that character set is not 8-bit, it is 16-bit. So we have a problem there as well. The 10.2 Character Set Support documentation states: "Oracle uses UTF-8 (8-bit) encoding by way of three database character sets, two for ASCII-based platforms (UTF8 and AL32UTF8) and one for EBCDIC platforms (UTFE). If you prefer to implement Unicode support incrementally, you can store Unicode data in either the UTF-16 or UTF-8 encoding form, in the national character set, for the SQL NCHAR datatypes (NCHAR, NVARCHAR2, and NCLOB)." If anyone's eyes are glazing over at all of this, I can sure sympathize! Character sets, Unicode, globalization, localization: these are well outside my comfort zone. Why can't we all just speak (and write) English? :-) I will take the following actions in light of this mistake: 1. Change the text of the choices to use "decimal representation" instead of "ASCII code". 2. Give everyone credit for a correct answer on that choice (and change your choice accordingly). 3. Rescore and recalculate rankings. 4. Change the assumptions so that the assumed character set is AL32UTF8. And as Ryan was the only player to report this issue, he wins his choice of an O'Reilly Media ebook. Thanks for the education, Ryan! Cheers, StevenSo: t

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.