I have a PL/SQL block where I:

1. Declare and initialize a collection (TABLE OF my_table.key%TYPE).
2. Use a SYS_REFCURSOR to BULK COLLECT keys into the collection.
3. Print lst.COUNT, which shows 0.
4. Run a FORALL loop to update rows in my_table based on the collected keys.
5. Print SQL%ROWCOUNT, which unexpectedly shows 1 row updated.

    DECLARE  
      TYPE t_list IS TABLE OF my_table.key%TYPE;  
      lst t_list := t_list();  
      cur SYS_REFCURSOR;  
      var NUMBER; -- Holds sequence value  
    
    BEGIN  
      -- Assign sequence value  
      var := my_sequence.NEXTVAL;  
    
      -- Open and fetch cursor  
      OPEN cur FOR  
        SELECT key FROM my_table WHERE some_conditions;  
    
      FETCH cur BULK COLLECT INTO lst;  
      CLOSE cur;  
    
      -- Debug output  
      DBMS_OUTPUT.PUT_LINE('lst.COUNT: ' || lst.COUNT); -- Prints 0  
    
      -- FORALL update  
      FORALL I IN 1..lst.COUNT  
        UPDATE my_table  
        SET col = SYSDATE  
        WHERE key = lst(I)  
        AND status = 'A';  
    
      DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT: ' || SQL%ROWCOUNT); -- Prints 1  
    
    END;

Observations & Issue:

• lst.COUNT = 0, so FORALL should not execute at all.
• However, SQL%ROWCOUNT shows 1 row updated instead of 0.
• If I remove the sequence.NEXTVAL assignment, SQL%ROWCOUNT correctly shows 0.

Questions:

1. Why does SQL%ROWCOUNT show 1 even when FORALL does not execute?
2. Does sequence.NEXTVAL affect SQL%ROWCOUNT in some way?
3. How can I ensure SQL%ROWCOUNT correctly reflects only the FORALL update?

I have a PL/SQL block where I:

1. Declare and initialize a collection (TABLE OF my_table.key%TYPE).
2. Use a SYS_REFCURSOR to BULK COLLECT keys into the collection.
3. Print lst.COUNT, which shows 0.
4. Run a FORALL loop to update rows in my_table based on the collected keys.
5. Print SQL%ROWCOUNT, which unexpectedly shows 1 row updated.

    DECLARE  
      TYPE t_list IS TABLE OF my_table.key%TYPE;  
      lst t_list := t_list();  
      cur SYS_REFCURSOR;  
      var NUMBER; -- Holds sequence value  
    
    BEGIN  
      -- Assign sequence value  
      var := my_sequence.NEXTVAL;  
    
      -- Open and fetch cursor  
      OPEN cur FOR  
        SELECT key FROM my_table WHERE some_conditions;  
    
      FETCH cur BULK COLLECT INTO lst;  
      CLOSE cur;  
    
      -- Debug output  
      DBMS_OUTPUT.PUT_LINE('lst.COUNT: ' || lst.COUNT); -- Prints 0  
    
      -- FORALL update  
      FORALL I IN 1..lst.COUNT  
        UPDATE my_table  
        SET col = SYSDATE  
        WHERE key = lst(I)  
        AND status = 'A';  
    
      DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT: ' || SQL%ROWCOUNT); -- Prints 1  
    
    END;

Observations & Issue:

• lst.COUNT = 0, so FORALL should not execute at all.
• However, SQL%ROWCOUNT shows 1 row updated instead of 0.
• If I remove the sequence.NEXTVAL assignment, SQL%ROWCOUNT correctly shows 0.

Questions:

1. Why does SQL%ROWCOUNT show 1 even when FORALL does not execute?
2. Does sequence.NEXTVAL affect SQL%ROWCOUNT in some way?
3. How can I ensure SQL%ROWCOUNT correctly reflects only the FORALL update?

• oracle-database
• plsql
• forall

1 Answer 1

Oracle did everything well; it is you who doesn't understand it.

SQL%ROWCOUNT shows 1, that's true - but it is related to this statement:

var := my_sequence.NEXTVAL;

Have a look:

SQL> DECLARE
  2     var  NUMBER;
  3  BEGIN
  4     var := seq_test.NEXTVAL;
  5
  6     DBMS_OUTPUT.PUT_LINE ('SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
  7  END;
  8  /
SQL%ROWCOUNT: 1

PL/SQL procedure successfully completed.

SQL>

Nothing else has been executed because cursor didn't fetch anything, FORALL wasn't executed so nothing (after the assignment statement) "reset" SQL%ROWCOUNT to some other value - so, the final result you got was 1 (as it should've been).

I guess you can now answer all 3 questions yourself.