This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,776 Users
  • 2,269,776 Discussions
  • 7,916,827 Comments

Discussions

Merge - Change Capture and Print

Rengudi
Rengudi Member Posts: 594 Bronze Badge

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

Tagged:

Answers

  • mathguy
    mathguy Member Posts: 11,041 Black Diamond

    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
    User_3ABCE Member Posts: 274 Gold Badge
    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