Merge - Change Capture and Print

Rengudi

Hi Guru,

Is there a method to print the values of insert, update, and remove before and after a MERGE statement has been executed?

If so, could you kindly give one example;

Thanks

mathguy

What is "the value of insert"? Also, what is "remove"? Do you mean "delete"? That is a very basic term in SQL; if you mean "delete", then say "delete".

It would help if you gave an example: the table before MERGE, then the MERGE statement (no need to tell us what the table looks like after MERGE - we can figure that out), and then the exact output you want from your question in this thread. What do you need to report? For example: Does your table have a primary key, and perhaps, as part of the requirement, you need to say which primary keys had changes? Which were deleted, which were added (via insert), and which had data updated? What else? What is the exact format of what you need?

User_3ABCE

create table t1(n1 number, n2 number);
insert into t1 select level, level*100 from dual connect by level<=3;
commit;

<<ab>>
declare
  lt  varchar2(100 char);
  ltr raw(16);
  scn number;
  type tr is record (op varchar2(1 char), n1 number, n2 number, n2_old number);
begin
  lock table t1 in share mode;
  lt := dbms_transaction.local_transaction_id(true);
  select t.xid, start_scn into ltr, scn
    from v$transaction t
    where t.xidusn||'.'||t.xidslot||'.'||t.xidsqn = ab.lt;
  --
  merge into t1 t
    using (   select 2 n1, 222 n2 from dual
    union all select 3, null from dual
    union all select 4, 444 from dual) s
    on (t.n1 = s.n1)
    when not matched then insert (n1, n2) values(s.n1, s.n2)
    when matched then update set n2 = s.n2
      delete where s.n2 is null;
  commit;
  --
  for c tr in (execute immediate '
            select versions_operation op, t.n1, t.n2, t2.n2 n2_old
            from t1 versions between scn minvalue and maxvalue t
            left join t1 as of scn ('||scn||') t2 on t2.n1 = t.n1
            where t.versions_xid = :ltr' using ltr) loop
    dbms_output.put_line('op='||c.op||' n1='||c.n1||' n2='||c.n2_old||'->'||case when c.op='D' then '' else c.n2 end);
  end loop;
end;
/

op=U n1=2 n2=200->222
op=D n1=3 n2=300->
op=I n1=4 n2=->444

select * from t1;

        N1         N2
---------- ----------
         4        444
         1        100
         2        222