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.

Autonomous Transactions and Deadlocks (2024)

A player wrote in response to the 17 February quiz:

You state that "The autonomous transaction blocks do not share locks with the "outer" session in which the first block was executed and locks were placed on rows". My question is then: why then does the dead lock occur? It must be exactly because the blocks DO share locks. Or what?? Could you elaborate more on, why the AUTONOMOUS_TRANSACTION declaration causes the deadlock yo occur. Wouldn't it be more precise if you said something like: The three blocks each have their own scope, and as such they do not block for each other (some argument with Oracle read consistency), and without autonomous transactions no deadlocks would occur, the AUTONOMOUS_TRANSACTIONs, causes the deadlocks to occur, (but why??). Also you should comment on the role of ROLLBACK in this quiz.

The quiz presented a set of three blocks, to be executed in the specified order, in the same session, simplified as follows:

BEGIN
   ...DML statements...
END;
/

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN   
  ...DML statements...
   ROLLBACK;
END;
/

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  ...DML statements...
   ROLLBACK;
       
END;
/

The first block executes and in the process locks, say, two rows. Those uncommitted changes and the associated locks are a part of the schema's transaction.

The second and third blocks, however, use the AUTONOMOUS_TRANSACTION pragma to specify that these blocks are to be executed as autonomous transactions. As stated in the Oracle documentation:

"An autonomous transaction is an independent transaction that can be called from another transaction, called the main transaction. You can suspend the calling transaction, perform SQL operations and commit or undo them in the autonomous transaction, and then resume the calling transaction.

"Autonomous transactions are useful for actions that must be performed independently, regardless of whether the calling transaction commits or rolls back. For example, in a stock purchase transaction, you want to commit customer data regardless of whether the overall stock purchase goes through. Additionally, you want to log error messages to a debug table even if the overall transaction rolls back.

"Autonomous transactions have the following characteristics:

• "The autonomous transaction does not see uncommitted changes made by the main transaction and does not share locks or resources with the main transaction.
  
• "Changes in an autonomous transaction are visible to other transactions upon commit of the autonomous transactions. Thus, users can access the updated information without having to wait for the main transaction to commit."
  

To conclude: each block executed in a session has its own scope in terms of identifiers declared in that block. From the standpoint of a transaction, however, all blocks are part of the same "main" transaction of the session - unless you use the AUTONOMOUS_TRANSACTION pragma.

I hope this clarifies the intent and lesson of the quiz.