0

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.

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).

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

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.

So finally: having trouble on how i could accomplish this.

Many thanks!

UPDATE:

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 AskTom I managed to get below:

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**
EkeshOkor
  • 109
  • 2
  • 10

1 Answers1

1

This is (admittedly) a trivial example but you could (as you suggested) put the affected row counts in a table then run a report off that table. 

declare
  row_cnt number;
begin
  update sales set job = 'DIRECTOR' where job = 'CLERK';
  row_cnt := sql%rowcount;  
  insert into sales_log values('Update', row_cnt);
  commit;

  exception
    when others then
      dbms_output.put_line('Error');
end;

Run your report off the table sales_log.

tale852150
  • 1,618
  • 3
  • 17
  • 23
  • Much appreciated - check my update in the question. Your help plus a bit more and i think i got it. Now to adapt everything i wrote so far. :) – EkeshOkor Aug 11 '16 at 21:57
  • @EkeshOkor - great, glad I could help. If you could, give me an upvote plz. :-) – tale852150 Aug 11 '16 at 22:01
  • of course, sorry about that. i had it stuck in my head that i need to wait for 2 days before :\ - but that's applicable to your own answer only – EkeshOkor Aug 11 '16 at 22:13