The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.

08 June 2010

Response to questions raised about 7 June quiz: %ROWTYPE(361)

A number of players got in touch with us regarding this quiz question:

"Which of the following statements accurately describes the effect of using the %ROWTYPE attribute to declare a record variable or parameter?"

We address the concerns people have raised below, but first the bottom line: we believe that there was no mistake or ambiguity in this quiz and we will not be rescoring.

1. One choice, which is correct, stated "The record defined with this attribute will have a field for each column in the table specified."

Two people raised a question about that choice. One person noted that you can use %ROWTYPE to declare a record based on a cursor, in which case you would have a field for each expression in the select list of the cursor. That is true, but the above statement still holds, since it clearly implies ("table specified") that a declaration of the form %ROWTYPE was used to declare the variable/parameter.

The second person asked: "Does %ROWTYPE attribute include virtual columns defined for the table?" And the answer is yes, as you can tell by running the following two statements:

I too had selected the last choice as correct along with the two other correct choices, thinking on the same line but I guess this option is more about testing how you interpret the statement correctly than testing your PL/SQL knowledge :) Just kidding.....I think your ability to interpret information is also as important as your programming knowledge.

I agree that rescoring is not necessary and that the answers provided are correct given a narrow interpretation of the question; however, I offer the following argument against the reason cited for rejecting the “collection” option as a correct answer:

Each member of a collection may be assigned a value and the value assigned may be changed; therefore each member of a collection is a variable. If a type is declared to be a collection of %ROWTYPE members then any variable declared to be of that type is a collection of variables of %ROWTYPE type.

Although the preceding reasoning requires a broader interpretation of the question than was intended, I submit that it is defensible. The intent that %ROWTYPE appear directly in the variable declaration, while not explicitly stated, is something that the players could be reasonably expected to infer from the question (unfortunately I chose a more liberal reading).

I find it interesting that many of the top scorers for the quarter apparently failed to pick one of the correct responses or picked one of the incorrect responses. Perhaps some of us have started to over-think our answers.

Rob, the statement you quoted is not “dodgy logic,” it is the application of definition; i.e., the intrinsic quality of a variable (permitting reassignment of value) is used to demonstrate that the members of a collection are variables. At no point do I argue that a collection is of the same type as its members. The purpose of the argument is to show that declaring a variable of type “collection of %ROWTYPE” implicitly declares variables of type %ROWTYPE. It is analogous to the object-oriented concept of composition.

The attempted analogy of eyes and cats does not mirror the critique; to do that it should have been “Each eye of a cat can see and can wink; therefore a cat is an eye.” However, even that does not correspond with the argument I presented.

As I stated in my previous post (though not as clearly as I had hoped), I disagree with the stated reason for rejecting, but not the rejection of, the “collection” option as a correct answer.

jhall, you say members of a collection are variables, and I say they are not: the collection itself is a variable, not its members. Even if you can do similar actions with them. A difference: a variable can have a datatype that is independent of other variables. Another: a variable always has to be explicitly declared (except in a cursor-for-loop).

Maybe the differences don't matter much in itself, but it's the reason why your reasoning did not resonate with me.

The answer starts with "Use %ROWTYPE to define a PL/SQL collection", which -to me- is always incorrect because %ROWTYPE is used to define a record variable, not a collection variable.

By the way, I still think the analogy is valid:

eye = membercat = collectioncan see = can be assigned a valuecan wink = can have its value changedanimal = variable

and of course a collection is a type of variable and cat is a type of animal.