19 February 2011

Changing value of iterator in FOR loop: Yes, you can! (2023)

In the 16 February quiz, we asked: "Which of the following statements about the iterator (also referred to as the index) of a FOR loop are correct?" And we scored this choice as incorrect: "You can change the value of the iterator, but the new value must fall within the low and high values specified in the FOR loop header."

The explanation given was as follows:

Any attempt to change the value of the iterator will cause the following compilation error to be raised:
PLS-00363: expression 'INDX' cannot be used as an assignment target
One player wrote with the following observation:

Just saw the answers of 16-feb-2011 quiz. Though I got the answer correct for the choice "You can change the value or the iterator, but the new value must fall within the low and high values specified for the FOR loop header", after reading the explanation, I realize that I got it right for a different reason.

The answer "any attempt to change the value of the iterator will cause compilation error" is not fully correct. It correct for Numeric for_loop. But for Cursor for_loop, you are allowed to change the value of iterator:
SQL>l
  1  declare
  2  r dual%rowtype;
  3  begin
  4  r.dummy := 'Y';
  5  for iterator in (select * from dual)
  6  loop
  7  iterator := null; <-- changed
  8  iterator := r;     <-- changed
  9  end loop;
 10* end;
SQL>/

PL/SQL procedure successfully completed.
Thanks for pointing this out, Spoon! I feel that the scoring of this choice as incorrect is still valid, due to the second part of the sentence. It strongly imply a numeric FOR loop ("low and high values"). Even, however, if we could agree on a sensible meangin for "low" and "high" records, there are no restrictions on the values you can set in the record so that rule could be violated.

What does this all mean? I will not be rescoring, but I will change the text of the choice to make clear I am talking about a numeric FOR loop. And I will create a quiz to use in the future regarding the ability to change a record iterator in a cursor FOR loop.

No comments:

Post a Comment