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

Question

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!

Is the code compiling. I can see it will not compile and throw issue. There is a missing ; at count:= count + SQL%ROWCOUNT — XING
– XING, Commented Feb 2, 2017 at 15:55
Hi @XING. Yes, its compiling is just that I manually wrote the count variable just to illustrate the problem. It seem I missed the ; — Dev Ngron
– Dev Ngron, Commented Feb 2, 2017 at 15:58

Gurwinder Singh · Accepted Answer · 2017-02-02 16:17:45Z

1

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;

edited Feb 2, 2017 at 16:17

answered Feb 2, 2017 at 15:58

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

2 Comments

Dev Ngron Over a year ago

Hi @GurV. Yes, in real scenario I used the variable row_count and got the same issue. I just wrote count to illustrate the problem; sorry for the confusion.

XING Over a year ago

He said the code is compiled. I guess he just gave an example showing that count is not working. if he would had used count he would had definetly got the compilation error.

XING · Accepted Answer · 2017-02-02 15:58:20Z

1

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;

answered Feb 2, 2017 at 15:58

XING

9,9264 gold badges25 silver badges40 bronze badges

2 Comments

Dev Ngron Over a year ago

As XING said, it seemed I had to re-arrange my code, because it was compiling just fine, but it wasn't returning the rowcount. @Gurwinder Singh 's approach worked for me as well as XING approach. Thanks for the help!

XING Over a year ago

However I would say we actually did not do anything.. Your code was working already and it had no issue. The demo and all the answers seems worthless for this question.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related