www .Just SQL. com  

A Site by Peter Robson, for all those with an interest in Oracle's SQL*Plus....

Comparing 2 tables on their non-PK fields

Site Map

--
-- 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('&COPY')
 order by column_id;
--
select '''-''' from dual;
--
select
 'from ','&COPY'||'&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('&COPY')
 order by column_id;
--
select '''-''' from dual;
--
select
 'from ','&COPY'
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('&COPY')||'):'
from dual;
--
set sqlprompt "SQL>"
set feedback on
set pagesize 999
set trims on
--
@ac.sql
--
undefine MASTER
undefine COPY
set linesize 80
    

 

e-Mail Contact