|
-- -- COMP_ALL_FIELDS.sql -- set echo off -- -- Peter Robson -- July 2003 -- -- Building a Table Comparison script --
-- Compares rows in two tables (MASTER and COPY) and identifies -- those rows which are unique to one site. Both tables -- must have the same structure. User is prompted to enter
-- each table name, as well as a database link IF the copy table -- is on a remote instance. Otherwise do not enter a value for -- the database link. -- -- Comparison is based on a field-by-field comparison.
-- All date fields are compared as 'trunc(date_field)'. -- -- NOTE that uniqueness may be on basis of two criteria - -- a) rows on other site are absent -- b) rows on other site are present but differ
-- This script will not distinguish between these two cases. -- Use a PK comparison to do that. -- set sqlprompt "" set trims on set pagesize 0 set feedback off set linesize 132 set verify off
undefine table -- prompt Enter name of tables to build comparison test against: accept MASTER prompt ' Master Table: ' accept COPY prompt ' Copy Table: '
accept DBLINK prompt ' Database Link (if used, and ONLY for Copy Table): ' -- -- Part 1, select from Master MINUS select from Copy ================ -- set termout off spool a.sql -- select 'select ' from dual;
-- select decode(data_type,'DATE','trunc('||column_name||')',column_name)||',' from user_tab_columns where table_name = upper('&MASTER') order by column_id; --
-- next dummy line to enable ALL column_name -- attributes to be followed by ',' -- select '''-''' from dual; -- select 'from ','&MASTER' from dual; -- select ' MINUS' from dual;
-- select 'select ' from dual; -- select decode(data_type,'DATE','trunc('||column_name||')',column_name)||',' from user_tab_columns where table_name = upper('©') order by column_id; --
select '''-''' from dual; -- select 'from ','©'||'&DBLINK;' from dual; -- spool off -- set termout on -- spool spooler.txt select
'spool &MASTER'||'_COPY.txt' from dual; spool off @spooler.txt -- select ' ' from dual; select 'Rows that are UNIQUE to Master table ('||upper('&MASTER')||'):' from dual; --
set sqlprompt "SQL>" set feedback on set pagesize 999 set trims on -- @a.sql -- spool off -- -- Part 2, select from COPY minus select from MASTER ================ -- set echo off
set sqlprompt "" set trims on set pagesize 0 set feedback off set verify off set termout off -- spool ac.sql -- select 'select ' from dual; -- select
decode(data_type,'DATE','trunc('||column_name||')',column_name)||',' from user_tab_columns where table_name = upper('©') order by column_id; -- select '''-''' from dual; -- select
'from ','©' from dual; -- select ' minus' from dual; -- select 'select ' from dual; -- select decode(data_type,'DATE','trunc('||column_name||')',column_name)||','
from user_tab_columns where table_name = upper('&MASTER') order by column_id; -- select '''-''' from dual; -- select 'from ','&MASTER;' from dual; -- set termout on -- spool off
-- select ' ' from dual; select 'Rows that are UNIQUE to Copy table ('||upper('©')||'):' from dual; -- set sqlprompt "SQL>" set feedback on set pagesize 999 set trims on --
@ac.sql -- undefine MASTER undefine COPY set linesize 80 |