More semantic analysis - and objections - in 28 April quiz (2244)

This quiz asked: "Which of these choices describe a way in which a value can be assigned to a PL/SQL variable?"

We scored as correct the following: "Change the value of a variable by passing it as an argument to a procedure or function." Why? Because you can define an OUT or IN OUT parameter and then change the value of the variable passed as an actual argument to that parameter within the body of either a procedure or function.

Several players wrote with objections like these:

"The variable is not actually changed by the act of passing it as a parameter to a procedure or function. It is actually being modified by the assignment that happens in the body of the procedure or function. I believe that your test code for that option is actually just another example of the first option (Assign a value to a variable using the assignment syntax)."


"A variable can be assigned by passing it as a parameter to a procedure, but it cannot be assigned by passing it as a parameter to a function, so it is ambiguous whether the last option should or should not be selected."


My responses:

First, you can define OUT and IN OUT parameters in a function's parameter list, so "procedure or function" in the question does not affect the correctness of the answer. Having said that, I recommend, as do many others, that you do not include OUT and IN OUT parameters in a function's parameter list. It restricts how you can use the function (cannot call it in an SQL statement, for example), but I also believe that your functions are easier to understand, use and maintain they it only passes data back through the RETURN clause.

Second, regarding the issue of whether and when the value of a variable passed as an argument is changed: the question asked "can be assigned" [my emphasis]. The word "can" means that, and I believe is fairly commonly interpreted as, "it is possible". It does not mean that every time you pass a variable as an argument it will be changed. It does not ask you to specify the mechanism by which the change takes place.

As I read over this post, I realize that it is likely that a number of players objected because they analyzed the correctness of the choice "Change the value of a variable by passing it as an argument to a procedure or function." independently of the question. If I asked you to answer true or false to "The value of a variable is changed by passing it as an argument to a procedure." I could see why you might say "It depends."

Remember, though, that you must evaluate each choice as an answer to the question, not as an independent statement. When this is done, the "can" of the question establishes that you must only determine if such a thing can (is possible) happen.

ORDER BY needed on SELECT for answers to be correct? (2243)

The 27 April quiz focused on associative arrays and creative use of string indexing to reorder data in a collection. It prompted a flurry of emails, most of which were along these lines:

"I think the bulk select should have an order by to ensure that one of the choices is either always right or always wrong. Without the order by, I don't think you can assume the code will work in a certain way.."

"The way I read this question is which block of code _will_ show the two dates from the hire column in the order shown. The query in the pl/sql block does not contain an order by clause - there is no way to guarantee the order of the rows selected by the query. None of the blocks of code can be guaranteed to show the rows in the order shown in the question. Had the question been phrased which block of code _may_ show the specified results, then some of the answers would be correct."

"The options are effectively: 1) order by to_char(hire_date, 'J') 2) order by to_char(hire_date, 'DD-MON-RR') as NLS assumptions have a DD-MON-RR date format 3) order by to_char(hire_date, 'YYYYMMDD') 4) no order by With the date provided (inserted in the order that it is), options 1 and 3 are definitely correct, option 2 is definitely incorrect and option 4 is technically indeterminate but practically incorrect given the insert order and no indexing (based on my prior experience - ie didn't test to confirm). While it is not a actual requirement, none of the options perform a true date sort (assuming BC data, although option 1 needs pre 7-Nov-1975 BC data)."

"While in this particular case it is almost 100% sure that performing the BULK SELECT without an ORDER BY will place the 2 rows into the l_plch_employee associative array in the same order as the 2 rows were inserted into the plch_employees table, this is in general NOT ensured by SQL. Therefore, the quiz choice with AnswerID=6054, that is most probably meant to be an incorrect one, is not 100% decidable, except if you add for example an "ORDER BY employee_id" to the BULK SELECT. Just a remark for SQL-purists, that might object on this issue."

And that wasn't all! One player wrote:

"Today's quiz appeared to have no correct answer. All of these answers, except for the 3 answer, contain DECLARE...BEGIN...END blocks. These cannot be used within the BEGIN...END section of an anonymous block. The 3rd answer does nothing to specify the order of the hire dates, and is therefore inadequate."

Finally, a couple of players wrote to complain about the sheer volume of code that had to be read to answer the quiz.

My responses:

1. Regarding the lack of ORDER BY: I'd actually rather invite answers from players first, before I offer my view on this. I am curious to see what you think.

2. Regarding the inclusion of DECLARE...BEGIN...END inside another BEGIN...END: actually, that is absolutely acceptable in PL/SQL. That is an example of a nested block. I generally avoid nested blocks in my code and instead create a nested or local subprogram (procedure or function). This means that instead of the following:

BEGIN
   some_code;

   DECLARE
      l_var   NUMBER;
   BEGIN
      l_var := 100;
   END;

   more_code;
END;

I would write the following:

DECLARE
   PROCEDURE set_l_var
   IS
      l_var   NUMBER;
   BEGIN
      l_var := 100;
   END;
BEGIN
   some_code;

   set_l_var;

   more_code;
END;

I find this approach much more readable and easier to maintain.

3. Regarding volume of code: generally I try to avoid lots of code repetition in the choices by isolating the common parts, putting them in the question and just including a comment in the question code that should be replaced by the code in the choices. In this case, however, three of them were very similar but the fourth was quite different, so I could not easily avoid the situation.

Looking forward to your thoughts....SF

How did you feel about the DBMS_XA quiz? (2242)

On 26 April, the PL/SQL Challenge offered its first quiz on the DBMS_XA package. As often happens with "out of the ordinary" features or packages, I received some notes from players expressing concern.

One person called the quiz a "fiasco", adding that " honestly cannot find a decent explanation in any of the documentation."

Another wondered how a person might go about answering the quiz, posing these options:

1.  Decide from the beginning that he simply does not know enough for answering it, and simply choose  "None of the answers are correct" AND also DO THIS VERY FAST ( an option that most of the times is likely to minimize the number of wrong answers and also acquire an almost-zero time, as we already discussed in large debates several good times in the past, even recently ),  and thus "gain" a not so bad score ... surely a much better one than his real knowledge ...

OR  ( and this is really a BIG OR  !!! )

2.  Deciding to invest  ( many would rather say ... "waste" ... considering that time does count much in this competition !!! ) a certain (rather longer than small) amount of time to look a little bit into the things and trying however to come up with as good an asnwer as possible, for a maybe completely new topic for him ... and then being "punished" for the high time and scoring even lower than a player taking the first action above ...

then concluding:

How deep are we expected to have dived into these very specialized features so that to be well prepared for what is still awaiting us during the PL/SQL Challenge ... so that to be able to still continue to play it without a feeling of total frustration ...that ... I dare bet ... will be experienced by not a few players (myself included) with today's quiz ...


Here are some of my thoughts:


1. Offering a quiz every day is quite a....challenge. If I only stick with "mainstream" functionality of PL/SQL, I can tell you right now that it will be very tough to continue to offer interesting new material. I also want to leverage the knowledge of developers from around the world, who know much more about various aspects of and applications of PL/SQL than I do.


2. While I appreciate that many players are very much in "competitive mode" at the PL/SQL Challenge, the most fundamental and important objective of the site is to deepen and broaden your knowledge of the PL/SQL language (including the many built-in packages that add to the base language).

3. You will undoubtedly encounter quizzes on topics with which you have no experience. And, yes, that means that almost certainly your score for that day will not be very high. But you should try not to be terribly frustrated by such quizzes and instead focus on the learning aspects.

Your thoughts?