Print the number of COMMITTED lines or/and commands executed

Question

In Oracle 9i, in a command line program. How to dbms_output.put_line the number of affected lines (updated/deleted/inserted) between the previous BEGIN and the last COMMIT?

Plus, how to print the number of executed instructions (DML, DDL)?

Answer 1

There's no easy way to get the number of statements executed, or a cumulative count of affected rows. You'll need to add code to track that yourself. For the number of statements you can just add one to a variable each time you execute. For the affected row count you can use the SQL%ROWCOUNT implicit cursor attribute:

declare
  statement_count pls_integer := 0;
  total_row_count pls_integer := 0;
begin
  insert into my_table (id) values (1);
  statement_count := statement_count + 1;
  total_row_count := total_row_count + SQL%ROW_COUNT;
  dbms_output.put_line('Rows affected by statement ' || statement_count
    || ': ' || SQL%ROWCOUNT);

  update my_table set id = id + 1;
  statement_count := statement_count + 1;
  total_row_count := total_row_count + SQL%ROW_COUNT;
  dbms_output.put_line('Rows affected by statement ' || statement_count
    || ': ' || SQL%ROWCOUNT);

  delete from my_table where id = 2;
  statement_count := statement_count + 1;
  total_row_count := total_row_count + SQL%ROW_COUNT;
  dbms_output.put_line('Rows affected by statement ' || statement_count
    || ': ' || SQL%ROWCOUNT);

  dbms_output.put_line('Number of statements: ' || statement_count);
  dbms_output.put_line('Total rows affected:  ' || total_row_count);
end;
/

You would need to reset the counter(s) after commit or rollback if you're only interested in committed values, and if you're going to be doing that mid-block; though that usually isn't necessary or a good idea.