CODEWITHSUNDEEP
oracle-databaseplsql
Dev Ngron
Dev Ngron

Reputation: 135

ORACLE SQL%ROWCOUNT doesn't work inside a cursor fetch

In my following code, I'm unable to get the inserted row count within a cursor fetch.

 ...
row_count NUMBER:= 0;
BEGIN
OPEN object_id_cur(id, c_bool);
            LOOP 
              FETCH object_id_cur INTO l_object_id;
              EXIT WHEN object_id_cur%NOTFOUND;

              INSERT INTO table1( 
                                  id, 
                                  num
                                  )
                     SELECT t2.r_object_id, 
                            t2.num                            
                       FROM table2
                      WHERE t2.r_object_id = l_object_id.r_object_id;
               row_count:= row_count + SQL%ROWCOUNT;
               -- I also tried dbms_output.put_line(SQL%ROWCOUNT); 
            END LOOP;
          CLOSE object_id_cur;
          COMMIT;
          dbms_output.put_line('insert count= ' || row_count || ' rows inserted...');
END;

My obtained result is: count = rows inserted... count is blank. If I move the insert outside the cursor fetch, then the row count works just fine. Is there a logical explanation for this? Thanks!

Upvotes: 0

Views: 10644

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

count is a reserved keyword. Use "count" or some other identifer say cnt. Also, add the missing semicolon at then end of the increment statement.

declare
  cnt number := 0;
begin
    . . . 
    cnt := cnt + SQL%ROWCOUNT;
    . . .

Demo:

SQL> create table t (id int);

Table created.

SQL> declare
  2    cursor c is
  3    select level n from dual connect by level <= 100;  -- produces 100 rows
  4    n int;
  5    cnt number := 0;
  6  begin
  7    open c;
  8    loop
  9      fetch c into n;
 10      exit when c%notfound;
 11      insert into t (id)
 12      select n from dual union all
 13      select n from dual;             -- insert each value twice
 14      cnt := cnt + sql%rowcount;
 15    end loop;
 16    close c;
 17    dbms_output.put_line(cnt);   -- should be 200
 18  end;
 19  /
200               -- correct output

PL/SQL procedure successfully completed.

SQL>

As you can see above, the SQL%rowcount works correctly. It could be that your below select query is not producing any rows.

SELECT t2.r_object_id, 
       t2.num                            
FROM table2
WHERE t2.r_object_id = l_object_id.r_object_id;

Upvotes: 1

XING
XING

Reputation: 9886

SQL%ROWCOUNT return the number of rows fetched/processed by the last DML executed. If the DML fails after fetching 1 row, due to any reason, SQL%ROWCOUNT will return only 1, the number of rows fetched/processed so far. It wont give you the total count. I did a simple PLSQL block and getting the SQL%ROWCOUNT working fine. Seems there is something else which is not working in your code. 

declare
   num   number := 0;
begin
   for i in 1 .. 10
   loop
      insert into a_table (id)
           values (i);

      num := num + sql%rowcount;
   end loop;

   dbms_output.put_line (num);
end;

Upvotes: 1

Related Questions