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)
  GENERATED ALWAYS AS (
     CASE
        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'
    END
  ) VIRTUAL
)
/

DECLARE
  l_rec   sales_with_vc%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line (l_rec.sale_category);
END;/

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

DECLARE
  TYPE coll_rowtype IS TABLE OF landen%ROWTYPE INDEX BY PLS_INTEGER;
  collection_landen  coll_rowtype;
  CURSOR c_lnd IS SELECT * FROM landen;
BEGIN
  FOR r_lnd IN c_lnd
  LOOP
     collection_landen (r_lnd.id) := r_lnd;
  END LOOP;
END;
/

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.