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:

CREATE TABLE sales_with_vc(
    sales_id    NUMBER
  , cust_id     NUMBER
  , sales_amt   NUMBER
  , sale_category VARCHAR2 (6)
        WHEN sales_amt <= 10000 THEN 'LOW'
        WHEN sales_amt > 10000 AND sales_amt <= 100000 THEN 'MEDIUM'
        WHEN sales_amt > 100000 AND sales_amt <= 1000000 THEN 'HIGH'
        ELSE 'ULTRA'

  l_rec   sales_with_vc%ROWTYPE;
  DBMS_OUTPUT.put_line (l_rec.sale_category);

2. A half dozen developers objected to us considering this statement to be wrong: "Use %ROWTYPE to define a PL/SQL collection of multiple rows of the specified type."

As one player put it, "Why is the last answer (use %rowtype for a collection) wrong? I tested this chunk of code and it works perfectly."

  collection_landen  coll_rowtype;
  CURSOR c_lnd IS SELECT * FROM landen;
  FOR r_lnd IN c_lnd
     collection_landen (r_lnd.id) := r_lnd;

And this player is right, you can use %ROWTYPE in a TYPE statement to "define a PL/SQL collection of multiple rows of the specified type."

The reason this choice is not a correct answer is that the question asked about "using the %ROWTYPE attribute to declare a record variable or parameter." Not a collection variable or parameter.

We look forward to your thoughts on this topic.


  1. Steven, Finn,

    I have read all published concerns and your answers, and until this far I have agreed every time with your explanations to either reject or approve a concern. And this time is no exception.

    Regarding these two concerns: I've seen far more debatable concerns than these.


  2. 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.

  3. 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.

  4. Once again, the question asked about "using the %ROWTYPE attribute to declare a record variable or parameter." We cannot see how that can be interpreted to cover collection variable/parameter.

  5. "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."

    That is dodgy logic. Just because two entities happen to share two common attributes doesn't mean they're equal. That's like saying:

    "Each eye of a cat can see and can wink, therefore each eye of a cat is an animal."

  6. 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.

  7. 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 = member
    cat = collection
    can see = can be assigned a value
    can wink = can have its value changed
    animal = variable

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

    But, let's agree to disagree :-)