Oracle - using SQL%ROWCOUNT for auditing purpose
<p>So i have a .NET software that makes use of Oracle as its database platform. A report in this software will call upon procedures from the Oracle schema that will run a number of update/inserts/deletes/merges on the database. </p>

<p>Now from research i can see that SQL%ROWCOUNT can be used to tell the queries to put the number of rows in the DBMS_OUTPUT - but the way i understand it that will work if i execute these procedures from a query software (ie: SQL Plus, TOAD, etc).</p>

<p>I need to find a way to display back to the report how many rows have been affected. </p>

<p>Now, if i'm not missing something basic, i guess the easiest way is to insert into an audit table SQL%ROWCOUNT of affected rows and then call it with the report once the procedures have finished their job. </p>

<p>So finally: having trouble on how i could accomplish this.</p>

<p>Many thanks! </p>

<p><strong>UPDATE:</strong></p>

<p>Ok. So my SPs were a bit complicated with cursors, dynamic SQL and more - as such with your idea and a bit of help from this topic from <a href="https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:497908700346519628" rel="nofollow">AskTom</a> I managed to get below:</p>

<pre><code>declare v_count number;
.......
loop
 fetch v_data into v_database_name;
 exit when v_data%NOTFOUND;
  sql_update := 'merge PL/SQL block';
  execute immediate 'begin ' || sql_update || '; :x := sql%rowcount; end;' using OUT v_count;
  update cw_script_audit set sparam = sparam||'//'||v_count where hmy = 5063; 
  commit;
  sql_alter := 'alter session close database link '||v_database_name;
  execute immediate sql_alter;
 commit;
end loop;
...........

  **update cw_script_audit set sparam = sparam||'//'||v_count where hmy = 5063;** 
  **This line updates a single static record for testing purposes**
</code></pre>