set echo off
--
--  TabDataDiff.sql    
--
--  Tables - Show data differences between 2 tables
--  Before running this script ensure set wrap is on 
--
--  Usage: @TabDataDiff11 table_name db_link1 db_link2     
--
set wrap on
set termout off
set verify off
set doc off
set feedback off
set recsep off
set pagesize 0
--
define table_name=&&1
define db_link1=&&2
define db_link2=&&3
column inst_code1 noprint new_value inst_code1
column inst_code2 noprint new_value inst_code2
column column_id noprint
define lsize=700
set linesize &&lsize
--
select value inst_code1
  from v$parameter&db_link1
where name = 'db_name'
/
select value inst_code2
  from v$parameter&db_link2
where name = 'db_name'
/
--
define out_file=TabDataDiff.ddl
spool &&out_file
--
select 'set heading off' from dual;
select 'set feedback off' from dual;
select 'select ''######################################################################################################################## '' from dual; ' from dual;
select 'select ''&&table_name Data differences: '' from dual; ' from dual;
select 'set feedback on' from dual;
select 'set heading on' from dual;
select decode(column_id,1,'SELECT ''1-&inst_code1'' inst_code, ',','), column_name, column_id
from user_tab_columns 
where table_name = upper('&&table_name')
order by column_id
;
select 'FROM &&table_name&&db_link1
MINUS' from dual;
select decode(column_id,1,'SELECT ''1-&inst_code1'' inst_code, ',','), column_name, column_id
from user_tab_columns 
where table_name = upper('&&table_name')
order by column_id
;
select 'FROM &&table_name&&db_link2 
UNION' from dual;
--
select decode(column_id,1,'SELECT ''2-&inst_code2'' inst_code, ',','), column_name, column_id
from user_tab_columns 
where table_name = upper('&&table_name')
order by column_id
;
select 'FROM &&table_name&&db_link2
MINUS' from dual;
select decode(column_id,1,'SELECT ''2-&inst_code2'' inst_code, ',','), column_name, column_id
from user_tab_columns 
where table_name = upper('&&table_name')
order by column_id
;
select 'FROM &&table_name&&db_link1
order by 1;' from dual;
--
spool off
set doc off
set feedback on
set pagesize 30
set termout on


-- to spool the output to a file uncomment the 2 lines before and after the start statement
-- spool tabdatadiff.out
start &&out_file
-- spool off


-- exit