For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
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
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?
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