oracle-databaseoracle9ioraclecommand

Print the number of COMMITTED lines or/and commands executed


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


Solution

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