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