CODEWITHSUNDEEP
sqloracle-databaseplsql
user6199575
user6199575

Reputation:

Oracle How to count total number of rows inserted in a plsql block

I want to count no of inserts I make into the database

The below query returns one(1) although 2 rows did update 

begin 
   Insert into APPLICATIONS (ID,ALIAS,NAME,STATUS) 
        values (1000000386,'BB','Branch Budgets','I'));
   Insert into APPLICATIONS (ID,ALIAS,NAME,STATUS) 
        values (1000000257,'TIME','Timesheets','I'));
   dbms_output.put_line('No Of rows'||sql%Rowcount);
 end;

Upvotes: 10

Views: 52652

Answers (1)

Utsav
Utsav

Reputation: 8093

dbms_output.put_line('No Of rows'||sql%Rowcount);

This will give you total number of rows updated by last statement. So even if you have 10 insert statements this way, you will always get 1 as sql%rowcount.

Either use 2 output statements, one each after insert statements, or use a variable and add number of rows updated to it, and then display it at last. 

declare
    v_count integer;
    begin 
        v_count:=0;
       Insert into APPLICATIONS (ID,ALIAS,NAME,STATUS) 
            values (1000000386,'BB','Branch Budgets','I');
        v_count:=   sql%Rowcount;
       Insert into APPLICATIONS (ID,ALIAS,NAME,STATUS) 
            values (1000000257,'TIME','Timesheets','I');
        v_count:= v_count+ sql%Rowcount;
       dbms_output.put_line('No Of rows '||v_count);
     commit;
     end;

OR If you are inserting data to same table, use a combined insert statement like this. This will return 2 rows.

begin    
    INSERT ALL 
        into  APPLICATIONS (ID,ALIAS,NAME,STATUS) 
             values (1000000386,'BB','Branch Budgets','I')
        into  APPLICATIONS (ID,ALIAS,NAME,STATUS) 
             values (1000000257,'TIME','Timesheets','I')
    SELECT * FROM dual;         
    dbms_output.put_line('No Of rows '||sql%Rowcount);
    commit;
    end;

Upvotes: 19

Related Questions