20 November 2010

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

19 November 2010

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

18 November 2010

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

17 November 2010

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