How Can I Calculate Aggregate Affected Rows If There Are Multiple Dml Query In My Plsql Block?

I have a scenario where there may exist multiple DML statements inside my PLSQL Block, I was looking for some generic approach by using which I can calculate total no of rows affec

Solution 1:

You may log the counts in a generic logging table using a generic procedure.

Logging table

CREATETABLE dml_logs (
     log_id      NUMBER PRIMARY KEY,
     step        VARCHAR2(200),
     row_count   NUMBER,
     log_date    DATE
);

Sequence for id

create sequence seq_dml_logs ;

Logging procedure

CREATEOR REPLACE PROCEDURE log_dml (
     p_step        VARCHAR2,
     p_row_count   NUMBER,
     p_log_date    DATE
) IS
     PRAGMA autonomous_transaction;
BEGININSERTINTO dml_logs (
          log_id,
          step,
          row_count,
          log_date
     ) VALUES (
          seq_dml_logs.NEXTVAL,
          p_step,
          p_row_count,
          p_log_date
     );
     COMMIT;
END;
/

PL/SQL block with DML

DECLARE
v_step dml_logs.step%TYPE;
BEGIN

  v_step :='cust_temp_a_update';
   UPDATE cust_temp_a SET name ='new_val'WHERE id =10;

 log_dml(v_step,SQL%ROWCOUNT,SYSDATE);

 v_step :='cust_temp_b_update';
   UPDATE cust_temp_b SET name ='new_val'WHERE id =20;

 log_dml(v_step,SQL%ROWCOUNT,SYSDATE);
END;
/

Then, aggregation is simple.

selectSUM(row_count) FROM dml_logs 
where step = ? and log_date = ? -- all the required conditions.

In order to better identify that the records belong to a particular run or a batch, you may add another column in the dml_logs called batch_number . Log this number to identify unique runs of your dmls and your query to get the aggregate details become much simpler.