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