René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Diff between tables in Oracle

set feedback off
set pages 5000

create table foo (
  a number,
  b varchar2(20)
);

insert into foo values (1,'one');
insert into foo values (2,'two');
insert into foo values (3,'three');
insert into foo values (4,'four');
insert into foo values (5,'five');
insert into foo values (6,'six');
insert into foo values (7,'seven');
insert into foo values (8,'eight');
insert into foo values (9,'nine');

commit;

create table foo_save as select rowid rowid_,foo.* from foo;

insert into foo values (10,'ten');
update foo set b = 'Nine' where a = 9;
delete from foo where a = 4;
update foo set b = 'Sixty six', a=66 where a=6;
delete from foo where a=2;
insert into foo values(20,'twenty');
update foo set b='Twenty' where a = 20;


select 
   max(case when c_ = 1 then s_ else 'upd' end) s_,
   max(case when c_ = 1 then    a else case when r_ = 1 then a else null end end) a,
   max(case when c_ = 1 then    b else case when r_ = 1 then b else null end end) b,
   max(case when c_ = 1 then null else case when r_ = 2 then a else null end end) a_changed,
   max(case when c_ = 1 then null else case when r_ = 2 then b else null end end) b_changed
  from (
  select row_number() over (partition by rowid_ order by s_ desc) r_, count(*) over (partition by rowid_) c_,rowid_, s_, a,b from (
    select * from
      (select 'del' s_, foo_save.* from foo_save minus select 'del' s_, rowid rowid_, foo.* from foo) union
    select * from
      (select 'add' s_,rowid rowid_, foo.* from foo minus select 'add' s_,foo_save.* from foo_save)
  )
)
group by rowid_;

drop table foo;
drop table foo_save;