Statement
with params as (
select
user owner_tab_1, 'TQ84_A' name_tab_1,
user owner_tab_2, 'TQ84_B' name_tab_2,
q'{grp = 'X'}' condition,
sys.dbms_debug_vc2coll('') excluded_columns -- Add columns that need to be excluded from the comparison
from
dual
),
col as (
select
listagg(a.column_name, ',') within group (order by b.column_id) as umns
from
params join
all_tab_columns a on a.owner = params.owner_tab_1 and
a.table_name = params.name_tab_1 join
all_tab_columns b on b.owner = params.owner_tab_2 and
b.table_name = params.name_tab_2
where
a.column_name = b.column_name and
-- a.column_name not in (select column_value from table(params.excluded_columns))
not exists (select column_value from table(params.excluded_columns) where column_value = a.column_name)
)
select
'select count(*) over () cnt_of_diffs, diffs.* from (' || chr(10) ||
q'[select 'only A' orig, only_a.* from (select ]' || col.umns || ' from ' || params.owner_tab_1 || '.' || params.name_tab_1 || case when params.condition is not null then ' where ' || params.condition end || ' minus ' ||
'select ' || col.umns || ' from ' || params.owner_tab_2 || '.' || params.name_tab_2 || case when params.condition is not null then ' where ' || params.condition end || ') only_a union all' || chr(10) ||
q'[select 'only B' orig, only_b.* from (select ]' || col.umns || ' from ' || params.owner_tab_2 || '.' || params.name_tab_2 || case when params.condition is not null then ' where ' || params.condition end || ' minus ' ||
'select ' || col.umns || ' from ' || params.owner_tab_1 || '.' || params.name_tab_1 || case when params.condition is not null then ' where ' || params.condition end || ') only_b' || chr(10) ||
') diffs
order by
' || col.umns || ',' || 'orig;'
from
params cross join
col;
Example
With the following two tables …
create table tq84_A (grp char(1), num number, txt varchar2(10), val number);
create table tq84_B (grp char(1), num number, txt varchar2(10) );
… the result of the select statement is the following SQL statement:
select count(*) over () cnt_of_diffs, diffs.* from (
select 'only A' orig, only_a.* from (select GRP,NUM,TXT from RENE.TQ84_A where grp = 'X' minus select GRP,NUM,TXT from RENE.TQ84_B where grp = 'X') only_a union all
select 'only B' orig, only_b.* from (select GRP,NUM,TXT from RENE.TQ84_B where grp = 'X' minus select GRP,NUM,TXT from RENE.TQ84_A where grp = 'X') only_b
) diffs
order by
GRP,NUM,TXT,orig;
Test data
Here's some data in order to test the generated SQL statement.
insert into tq84_a values ('X', 1, 'one' , 2.3 );
insert into tq84_b values ('X', 1, 'one' );
insert into tq84_a values ('X', 2, 'TWO' , 4.1 );
insert into tq84_b values ('X', 2, 'two' );
insert into tq84_a values ('X', 3, 'three', 2.9 );
insert into tq84_b values ('X', 3, 'three' );
insert into tq84_a values ('X', 4, 'four' , null );
insert into tq84_b values ('X', 5, 'five' );
insert into tq84_a values ('Y', 7, 'seven', 5.7 );
insert into tq84_b values ('Y', 9, 'nine' );
insert into tq84_b values ('Y', 8, 'eight' );