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