|
Tip of the Week
Tip for Week of January 25, 2004
Comparing Two Tables: Highlighting the Differences
This tip comes from
Sanjay Ray, Oracle Applications Technical Consultant in Sydney, Austrailia.
This is an extension to the code tip that I supplied on 21 December 2003,
"Comparing Contents of Two Tables with Identical Structure."
The following script highlights the columns that are different
in the two tables, so there is no need to compare each column
individually. The columns values may optionally be displayed
as well.
This script generates a spool file that can be run from a
SQL*Plus session to list the differences between 2 tables
with identical structure.
This can be useful if you need to compare
two similar tables across different schemas (or different databases,
in which case you will need to create a local view using a database
link), or to compare a table with itself at different points in time,
such as before and after an update through an application to track
how a table is affected. It reports whether a record is present in
one but not in the other table, or if the same key is present in
both tables but non-key columns have different values.
undefine TABLE1
undefine TABLE2
define g_table1 = '&&TABLE1'
define g_table2 = '&&TABLE2'
set verify off
set feedback off
set serveroutput on size 1000000
set linesize 132
set trimspool on
spo temp_file.sql
declare
v_owntab1 varchar2(255) := '&&g_table1';
v_owntab2 varchar2(255) := '&&g_table2';
v_own1 varchar2(255);
v_own2 varchar2(255);
v_tab1 varchar2(255);
v_tab2 varchar2(255);
v_dot1 number := 0;
v_dot2 number := 0;
type t_cols is table of varchar2(255) index by binary_integer;
v_cols1 t_cols;
v_cols2 t_cols;
v_keys t_cols;
v_out1 varchar2(255);
v_out2 varchar2(255);
kq CONSTANT varchar2(1) := '''';
v_ind number := 0;
v_str varchar2(2000):=null;
v_ind_found boolean := FALSE;
v_ind_colno number := 0;
v_is_key boolean := FALSE;
v_nonkey number := 0;
procedure print_cols (p_cols in t_cols) is
begin
for i in 1..p_cols.count
loop
dbms_output.put_line(','||p_cols(i)); end loop;
end print_cols;
begin
v_dot1 := instr(v_owntab1, '.');
v_dot2 := instr(v_owntab2, '.');
if v_dot1 > 0 then
v_own1 := upper(substr(v_owntab1, 1, v_dot1-1));
v_tab1 := upper(substr(v_owntab1, v_dot1+1));
else
v_own1 := null;
v_tab1 := upper(v_owntab1);
end if;
if v_dot2 > 0 then
v_own2 := upper(substr(v_owntab2, 1, v_dot2-1));
v_tab2 := upper(substr(v_owntab2, v_dot2+1));
else
v_own2 := null;
v_tab2 := upper(v_owntab2);
end if;
select column_name
bulk collect into v_cols1
from all_tab_columns
where table_name = v_tab1
and owner = nvl(v_own1, user)
order by column_id;
select column_name
bulk collect into v_cols2
from all_tab_columns
where table_name = v_tab2
and owner = nvl(v_own2, user)
order by column_id;
if v_cols1.count = 0 or v_cols2.count = 0 then
dbms_output.put_line('Either or Both the tables are invalid');
return;
end if;
--Outer select
dbms_output.put_line('select * from ( ');
--Main select (key attributes)
dbms_output.put_line('select ');
for c1 in (
select b.column_name
from all_indexes a, all_ind_columns b
where a.owner=b.index_owner
and a.index_name=b.index_name
and a.uniqueness = 'UNIQUE'
and a.table_owner = nvl(v_own1, user)
and a.table_name = v_tab1
order by b.index_name, b.column_position
)
loop
v_ind_found := TRUE;
v_keys(nvl(v_keys.count, 0)+1):=c1.column_name;
dbms_output.put_line('nvl(a.'||c1.column_name||', b.'||c1.column_name||') '||c1.column_name||',');
end loop;
if not v_ind_found then
v_keys(nvl(v_keys.count, 0)+1):=v_cols1(1);
dbms_output.put_line('nvl(a.'||v_cols1(1)||', b.'||v_cols1(1)||') '||v_cols1(1)||',');
end if;
--Identifier column to indicate if the key is present in either, or the other or both tables
dbms_output.put_line('decode(a.'||v_cols1(1)||', null, '||kq||'TAB2'||kq
||', decode(b.'||v_cols1(1)||', null, '||kq||'TAB1'||kq||', '||kq||'BOTH'||kq||')) WHICH, ');
--Main select (non-key attributes)
for i in 1..v_cols1.count
loop
v_is_key:=FALSE;
--If the column is a key column, it should be excluded
for j in 1..v_keys.count
loop
if v_cols1(i)=v_keys(j) then
v_is_key:=TRUE;
end if;
end loop;
if not v_is_key then
v_nonkey:=v_nonkey+1;
--The following code has 2 sections. Only one can be activated at any time
--Presently Section 1 is active, i.e. only column list will be displayed
-- To display column list without values, uncomment Section 1
--Start of Section 1 : Column List only. Without values
if v_nonkey = 1 then
dbms_output.put_line('decode(a.'||v_cols1(i)||',b.'||v_cols1(i)||', null,'||kq||v_cols1(i)||kq||')');
else
dbms_output.put_line('||'||kq||','||kq||'||'||'decode(a.'||v_cols1(i)||',b.'||v_cols1(i)||', null,'||kq||v_cols1(i)||kq||')');
end if;
--End of Section 1 : Column List without values
/*
--To display column list with values, uncomment Section 2
--Start of Section 2 : Column List with values
if v_nonkey = 1 then
dbms_output.put_line('decode(a.'||v_cols1(i)||',b.'||v_cols1(i)||', null,'||kq||v_cols1(i)
||'=('
||kq||'||a.'||v_cols1(i)||'||'||kq||','
||kq||'||b.'||v_cols1(i)||'||'||kq||')'||kq
||')');
else
dbms_output.put_line('||'||kq||','||kq||'||'||'decode(a.'||v_cols1(i)||',b.'||v_cols1(i)||', null,'||kq||v_cols1(i)
||'=('
||kq||'||a.'||v_cols1(i)||'||'||kq||','
||kq||'||b.'||v_cols1(i)||'||'||kq||')'||kq
||')');
end if;
--End of Section 2 : Column List with values
*/
end if;
end loop;
dbms_output.put_line('COL_LIST');
--from clause
dbms_output.put_line('from '||nvl(v_own1, user)||'.'||v_tab1||' a ');
dbms_output.put_line('full outer join '||nvl(v_own2, user)||'.'||v_tab2||' b ');
dbms_output.put_line('on (');
--Where condition
for i in 1..v_keys.count
loop
if i = 1 then
dbms_output.put_line('a.'||v_keys(i)||'=b.'||v_keys(i));
else
dbms_output.put_line('and a.'||v_keys(i)||'=b.'||v_keys(i));
end if;
end loop;
dbms_output.put_line(')');
--Close Outer select
dbms_output.put_line(') ');
dbms_output.put_line('where WHICH = '||kq||'TAB1'||kq);
dbms_output.put_line('or WHICH = '||kq||'TAB2'||kq);
dbms_output.put_line('or (WHICH = '||kq||'BOTH'||kq||' and ltrim(rtrim(replace(COL_LIST,'||kq||','||kq||',null))) is
not null)');
--We shall order by the key columns
dbms_output.put_line('order by');
for i in 1..v_keys.count
loop
if i=v_keys.count then
dbms_output.put_line(i);
else
dbms_output.put_line(i||',');
end if;
end loop;
dbms_output.put_line(';');
end;
/
spo off
set feedback on
Testing:
--Setup of Test Data
drop table test1;
drop table test2;
create table test1 (f1 number unique, f2 number unique, f3 number, f4 varchar2(10), f5 date);
create table test2 (f1 number, f2 number, f3 number, f4 varchar2(10), f5 date);
insert into test1 values (1, 10, 100, 'A1000', to_date('01011901', 'DDMMRRRR'));
insert into test1 values (2, 20, null, null, to_date('01011901', 'DDMMRRRR'));
insert into test1 values (3, 30, 300, 'A3000', to_date('01011901', 'DDMMRRRR'));
insert into test1 values (4, 40, 400, 'A4000', to_date('01011901', 'DDMMRRRR'));
insert into test1 values (7, 70, 700, 'A7000', to_date('01011901', 'DDMMRRRR'));
insert into test2 values (1, 10, -100, 'A1000', to_date('01011901', 'DDMMRRRR'));
insert into test2 values (2, 20, null, null, to_date('01011901', 'DDMMRRRR'));
insert into test2 values (3, 30, -500, 'A5000', to_date('01011901', 'DDMMRRRR'));
insert into test2 values (6, 60, -600, 'A6000', to_date('01011901', 'DDMMRRRR'));
insert into test2 values (7, 70, 700, 'A7000', to_date('01011901', 'DDMMRRRR'));
Run the script and pass the table names TEST1 and TEST2 when prompted.
Run the spool file temp_file.sql from SQL*Plus:
SQL> @temp_file
F1 F2 WHICH COL_LIST
1 10 BOTH F3,,
3 30 BOTH F3,F4,
4 40 TAB1 F3,F4,F5
4 60 TAB2 F3,F4,F5
7 70 BOTH ,,F5
F1 F2 WHICH COL_LIST
1 10 BOTH F3=(100,-100),,
3 30 BOTH F3=(300,-500),F4=(A3000,A5000),
4 40 TAB1 F3=(400,),F4=(A4000,),F5=(01-JAN-01,)
4 60 TAB2 F3=(,-600),F4=(,A6000),F5=(,01-JAN-01)
7 70 BOTH ,,F5=(01-JAN-01,02-JAN-01)
1. Parameters can be in either schema.table_name or
table_name format (in which case current schema
is assumed).
2. By default, the script lists non-key columns using
the key columns as the basis of comparison. If you don't
need all the columns, edit the spool file to comment out
the columns that are not required. If key column is being
removed, remove the column from the "order by" list as well.
3. Output is sorted on the key columns. It determines the
key list by querying the data dictionary ALL_INDEX and ALL_IND_COLUMNS
views for the FIRST table passed as input. If no unique indexes are
found, then ordering is based always on the first column of the tables.
(In the test case, columns F1 and F2 have been defined as Unique keys
of the first table.) So if one of the tables is located in a remote
database, make it the second table when generating the spool file.
4. This script was tested for scalar values, for example, number,
varchar2, and date. The script may not work for for long, LOB and Object Types.
5. If the key value is present in either of the tables but not both
(the value of the WHICH column is TAB1 or TAB2), then the column
list will include all columns in the table. If WHICH=BOTH, then only
columns with different values are displayed.
6. The script would normally print the column list without the
values. There is, however, a provision to output the values as well.
To display the column values, comment out the segment of code marked
?Section 1? and uncomment ?Section 2?. This may affect performance
for large tables.
7. No Key column may have null values in both tables.
8. The first Key column may not be null in either table.
|
Bookmark
