ORACLE Merge SQL Logging

general advice
don't put a commit in the procedure ...

use a trigger on the changed table tmp_cd_01 to post a record in a log table

you could also create a materialized_view_log on table tmp_cd_01
and changes will be recorded into that mvl

I agree with Geert that usually you should not put a commit inside a procedure.  Usually it is best to allow the calling program to control commits.

I don't know of a way in a merge statement to log how many records get inserted compared to how many get updated.  One option would be to write a separate function that you call first.  This function would need a cursor with a "where" clause like the "on" clause in your merge.  This function could return a "U" if the query finds a matching row, and an "I" when it doesn't.

I have three concerns with your merge statement:
1. This won't change anything: "UPDATE SET
       D.c_date = D.c_date"
    Maybe that should be: "UPDATE SET
       D.c_date = S.c_date"?

2. The first "WHERE S.d_ind = 1"
    should maybe be: "WHERE D.d_ind = 1"

3. You cannot delete from the source table like this: "DELETE
       WHERE S.d_ind = 1"
    According to the Oracle12 documentation: (https://docs.oracle.com/database/121/SQLRF/statements_9017.htm#SQLRF01606)
"Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation."

1. Use after update/insert/delete trigger on that table, if you want to log
2. Do not use Commit
3. If PL/SQL then can use if then else and insert entry in your log table

I assumed you want to know changed record in target table, not source table. So;
You can add two column on target table named as INSERT_DATE, UPDATE_DATE
In insert part of merge statement use SYSDATE  as  insert_date
In update part of merge statement Set update_date as SYSDATE

Something like this

MERGE INTO tmp_cd_01 d
     USING (SELECT * FROM tmp_cd_02) s
        ON (d.i_id = s.i_id AND d.e_id = s.c_no)
WHEN MATCHED THEN
   UPDATE SET d.c_date = d.c_date, d.update_date = SYSDATE
           WHERE s.d_ind = 1
   DELETE
           WHERE s.d_ind = 1
WHEN NOT MATCHED THEN
   INSERT     (i_id
             , e_id
             , e_date
             , l_code
             , c_date
             , insert_date)
       VALUES (s.i_id
             , s.c_no
             , s.e_date
             , s.l_code
             , s.c_date
             , SYSDATE)
        WHERE s.d_ind = 0;

Select allOpen in new window