Does sql%rowcount attribute work in loop condition?

Question

I have written the below program in PL/SQL to insert 5 rows in Students table. As per the loop condition the row count should be 5 but it is showing only 1. Placing DBMS output under the loop also didn't help.

DECLARE
    v_input_1 INT;
    v_input_2 VARCHAR2(15);
    v_input_3 VARCHAR2(10);
    v_counter NUMBER := 10;
BEGIN
    v_input_1:= 0;
    v_input_2:= &Type_student_name;
    v_input_3:= &Type_student_class;
        LOOP
            INSERT INTO STUDENTS(id, student_name, student_class)
            VALUES(v_input_1+v_counter, v_input_2, v_input_3);
            v_counter:=v_counter+10;
            EXIT WHEN V_counter > 50;
    --DBMS_OUTPUT.PUT_LINE('Total rows inserted : '||SQL%ROWCOUNT);
        END LOOP;
    DBMS_OUTPUT.PUT_LINE('Total rows inserted : '||SQL%ROWCOUNT);
END;

"The values of the cursor attributes always refer to the most recently executed SQL statement. The SQL%ROWCOUNT attribute is not related to the state of a transaction." Have a look into the oracle doc: docs.oracle.com/cd/B19306_01/appdev.102/b14261/… — hotfix
– hotfix, Commented Mar 5, 2020 at 10:08

Littlefoot · Accepted Answer · 2020-03-05 10:22:36Z

1

In your example, as you are inserting one row at a time (insert into ... values ...), SQL%ROWCOUNT is set to 1 in every iteration.

Therefore, it is pretty much useless in this context. Create a local variable and increment it every time (similar to what you're doing with v_counter), e.g.

declare
  l_cnt number := 0;
begin
  loop
    insert ...
    l_cnt := l_cnt + sql%rowcount;
  end loop;

  dbms_output.put_line('Number of inserted rows = ' || l_cnt);
end;

edited Mar 5, 2020 at 10:22

answered Mar 5, 2020 at 10:13

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Littlefoot Over a year ago

That would be even better, @Barbaros, for other cases where rows affected isn't 1. Thank you, fixed.

Collectives™ on Stack Overflow

Does sql%rowcount attribute work in loop condition?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related