|
set echo off
--
-- COMPARE_PK_FIELDS.SQL
--
-- Peter Robson
-- July 2003.
--
-- Script compares two tables on basis of their Primary Key.
-- Assumption - that both tables have identical PKs, in name and definition.
-- Other fields are not examined.
--
-- Output are the PKs of the rows unique to each table, presented in turn.
-- An intermediate table (xxx_compare_pk_keys) is built to hold column
-- constraint info, purely to avoid tedious joins across user_constraints
-- and user_cons_columns (always a slow process) as PKs are constructed.
-- The PK-build script refers to this intermediate table only. This
-- temporary table is deleted once comparison is complete.
--
-- Structure is:
--
-- select pk1,pk2,pkn from MASTER ... First comparison, Part 1
-- minus
-- select pk1,pk2,pkn from COPY; ... First comparison, Part 2
--
-- repeat the above in reverse
--
-- select pk1,pk2,pkn from COPY ... Second comparison, Part 1
-- minus
-- select pk1,pk2,pkn from MASTER; ... Second comparison, Part 2
--
-- Note - there is a lot of repetition throughout this script, which might
-- be handled better by calling a series of sub-scripts as the same
-- repeated process is required. For reasons of clarity the entire script
-- is presented in full.
------------------------------
set pagesize 0
set linesize 2000
set trims on
set feedback off
set verify off
set scan on
--
-- device to ensure both spool files are empty
--
set sqlprompt "--"
spool a_master.sql
spool off
set sqlprompt ''
--
set sqlprompt "--"
spool a_copy.sql
spool off
set sqlprompt ''
--
-----------------------
--
-- insert a blank line before table prompts
--
select ' ' from dual;
--
-- Prompt for two tables to be compared:
--
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): '
--
set termout off
-----------------------
-- Build temp table xxx_compare_pk_keys to hold constraint info,
-- to save repeated table joins while constructing the PKs
-- This table dropped at end of script.
--
create table xxx_compare_pk_keys as
select cc.owner,cc.constraint_name,cc.table_name,cc.column_name,position from user_cons_columns cc,
user_constraints uc
where cc.owner = uc.owner
and cc.constraint_name = uc.constraint_name
and uc.constraint_type = 'P'
and uc.table_name = upper('&MASTER');
------------------------
--
-- Build first half of process, testing MASTER uniqueness
--
spool a_master.sql
--
-- First comparison, Part 1.
--
-- 1st column where there is more than 1 column in the PK (1)
--
select
'select '
from dual;
--
select
' '||column_name
from
xxx_compare_pk_keys
where
position =
(select min(position)
from xxx_compare_pk_keys
)
and position <
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (2)
--
-- where 1st column is the only column
--
select
' '||column_name
from
xxx_compare_pk_keys
where
position =
(select min(position)
from xxx_compare_pk_keys
)
and position =
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (3)
--
-- where these columns are neither the first nor the last columns
--
select
' , '||column_name
from
xxx_compare_pk_keys
where
position >
(select min(position)
from xxx_compare_pk_keys
)
and position <
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (4)
--
-- where this column is the last, but not the first
--
select
' , '||column_name
from
xxx_compare_pk_keys
where
position >
(select min(position)
from xxx_compare_pk_keys
)
and position =
(select max(position)
from xxx_compare_pk_keys
)
;
--
-- build the phrase < from table_name >
--
select
'from &MASTER' from dual;
--
-- Now the binding component , 'minus' ----------------- M I N U S
--
select 'minus ' from dual;
--
--
-- Repeat the previous construct for the second comparison table
--
-- First comparison, Part 2; Building up the second half of the comparison script
--
--
-- 1st column where there is more than 1 column in the PK
--
select
'select '
from dual;
--
select
' '||column_name
from
xxx_compare_pk_keys
where
position =
(select min(position)
from xxx_compare_pk_keys
)
and position <
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (2)
--
-- where 1st column is the only column
--
select
' '||column_name
from
xxx_compare_pk_keys
where position =
(select min(position)
from xxx_compare_pk_keys
)
and position =
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (3)
--
-- where these columns are neither the first nor the last columns
--
select
' , '||column_name
from
xxx_compare_pk_keys
where
position >
(select min(position)
from xxx_compare_pk_keys
)
and position <
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (4)
--
-- where this column is the last, but not the first
--
select
' , '||column_name
from
xxx_compare_pk_keys
where
position >
(select min(position)
from xxx_compare_pk_keys
)
and position =
(select max(position)
from xxx_compare_pk_keys
)
;
--
-- build the phrase < from table_name >
--
select 'from '||'©'||'&DBLINK ;' from dual;
--
--
spool off
--
--------------- end of First comparison -----------------------------
--
-- Build second half of process, testing MASTER uniqueness
--
spool a_copy.sql
--
-- Second comparison, Part 1.
--
-- 1st column where there is more than 1 column in the PK (1)
--
select
'select '
from dual;
--
select
' '||column_name
from
xxx_compare_pk_keys
where
position =
(select min(position)
from xxx_compare_pk_keys
)
and position <
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (2)
--
-- where 1st column is the only column
--
select
' '||column_name
from
xxx_compare_pk_keys
where
position =
(select min(position)
from xxx_compare_pk_keys
)
and position =
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (3)
--
-- where these columns are neither the first nor the last columns
--
select
' , '||column_name
from
xxx_compare_pk_keys
where
position >
(select min(position)
from xxx_compare_pk_keys
)
and position <
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (4)
--
-- where this column is the last, but not the first
--
select
' , '||column_name
from
xxx_compare_pk_keys
where
position >
(select min(position)
from xxx_compare_pk_keys
)
and position =
(select max(position)
from xxx_compare_pk_keys
)
;
--
-- build the phrase < from table_name >
--
select
'from ©'||'&DBLINK' from dual;
--
-- Now the binding component , 'minus' ----------------- M I N U S
--
select 'minus ' from dual;
--
--
-- Repeat the previous construct for the second comparison table
--
-- Second comparison, Part 2; Building up the second half of the comparison script
--
--
-- 1st column where there is more than 1 column in the PK
--
select
'select '
from dual;
--
select
' '||column_name
from
xxx_compare_pk_keys
where
position =
(select min(position)
from xxx_compare_pk_keys
)
and position <
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (2)
--
-- where 1st column is the only column
--
select
' '||column_name
from
xxx_compare_pk_keys
where
position =
(select min(position)
from xxx_compare_pk_keys
)
and position =
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (3)
--
-- where these columns are neither the first nor the last columns
--
select
' , '||column_name
from
xxx_compare_pk_keys
where
position >
(select min(position)
from xxx_compare_pk_keys
)
and position <
(select max(position)
from xxx_compare_pk_keys
)
;
--
--
-- building up the PK clause (4)
--
-- where this column is the last, but not the first
--
select
' , '||column_name
from
xxx_compare_pk_keys
where
position >
(select min(position)
from xxx_compare_pk_keys
)
and position =
(select max(position)
from xxx_compare_pk_keys
)
;
--
-- build the phrase < from table_name >
--
select 'from '||'&MASTER'||' ;' from dual;
--
--
spool off
--
--
--------------- end of Second comparison.
set termout off
--
--
spool spooler.txt
select
'spool &MASTER'||'_COPY.txt' from dual;
spool off
@spooler.txt
--
------------------ Complete scripts written; drop intermediat
-- table 'xxx_compare_pk_keys' and run the scripts.
--
drop table xxx_compare_pk_keys;
--
set termout on
--
select ' ' from dual;
select
'Rows in '||upper('&MASTER')||' defined by PK that are absent from '||upper('©')||' :'
from dual;
--
set sqlprompt "SQL>"
set feedback 6
set pagesize 999
set trims on
--
@a_master.sql
--
set pagesize 0
--
select
'Rows in '||upper('©')||' defined by PK that are absent from '||upper('&MASTER')||' :'
from dual;
--
set pagesize 999
--
@a_copy.sql
--
set heading on
set trims off
set scan on
set feedback on
set verify on
set pagesize 999
set sqlprompt 'SQL>'
set echo on
|
