Compare two table data
                                                 Last update (2008-02-17 14:51:10)
                                                                                                                   Date added (2008-02-17 14:22:19)

Summary
This can be useful if 2 similar tables need to be compared across different schemas (or different databases, in which case a local view will need to be created using database link), or to compare a table with itself at different points in time, for example, before and after an update through Applications to track how a table is affected. The script indicates whether a record is present in one table but not in the other, or if the same key is present in both tables but non-key columns have different values.

Example tables and data to test

CREATE TABLE A (
    C1    NUMBER NOT NULL,
    C2    VARCHAR2(2000),
    C3    CHAR(255)
    );
	
CREATE UNIQUE INDEX A_INDEX ON A (C1);

CREATE TABLE B (
    C1    NUMBER NOT NULL,
    C2    VARCHAR2(2000),
    C3    CHAR(255)
    );
	
CREATE UNIQUE INDEX B_INDEX ON B (C1);

INSERT INTO A(C1, C2, C3) VALUES(1, 'A_ROW_001', 'TRUE');
INSERT INTO A(C1, C2, C3) VALUES(2, 'A_ROW_002', 'FALSE');
INSERT INTO A(C1, C2, C3) VALUES(3, 'A_ROW_003', 'TRUE');
INSERT INTO A(C1, C2, C3) VALUES(4, 'A_ROW_004', 'FALSE');

INSERT INTO B(C1, C2, C3) VALUES(1, 'B_ROW_001', 'TRUE');
INSERT INTO B(C1, C2, C3) VALUES(2, 'A_ROW_002', 'FALSE');
INSERT INTO B(C1, C2, C3) VALUES(3, 'B_ROW_003', 'TRUE');
INSERT INTO B(C1, C2, C3) VALUES(4, 'A_ROW_004', 'FALSE');

COMMIT;

Now run the script

(SELECT 'TAB1',C1,C2,C3
FROM SYS.A
MINUS SELECT 'TAB1',C1,C2,C3
FROM SYS.B)
UNION(
SELECT 'TAB2',C1,C2,C3
FROM SYS.B MINUS
SELECT 'TAB2',C1,C2,C3
FROM SYS.A)
ORDER BY C1;

If you want to "create the SELECT statement, for comparing two identical structured tables" use this script

undefine TABLE1
undefine TABLE2
define g_table1 = '&&TABLE1'
define g_table2 = '&&TABLE2'
set verify off
set feedback off
set serveroutput on size 1000000
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_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;
  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;

  dbms_output.put_line('(');
  dbms_output.put_line('select '||kq||'TAB1'||kq);
  print_cols(v_cols1);
  dbms_output.put_line(' from '||nvl(v_own1, user)||'.'||v_tab1);
  dbms_output.put_line('MINUS');
  dbms_output.put_line('select '||kq||'TAB1'||kq);
  print_cols(v_cols1);
  dbms_output.put_line(' from '||nvl(v_own2, user)||'.'||v_tab2);
  dbms_output.put_line(')');

  dbms_output.put_line('UNION');

  dbms_output.put_line('(');
  dbms_output.put_line('select '||kq||'TAB2'||kq);
  print_cols(v_cols1);
  dbms_output.put_line(' from '||nvl(v_own2, user)||'.'||v_tab2);
  dbms_output.put_line('MINUS');
  dbms_output.put_line('select '||kq||'TAB2'||kq);
  print_cols(v_cols1);
  dbms_output.put_line(' from '||nvl(v_own1, user)||'.'||v_tab1);
  dbms_output.put_line(')');

  dbms_output.put_line('order by ');
  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_ind_colno := v_ind_colno + 1;
    if v_ind_colno = 1 then
      dbms_output.put_line(c1.column_name);
    else
      dbms_output.put_line(','||c1.column_name);
    end if;
  end loop;
  if not v_ind_found then
    dbms_output.put_line('2 ');
  end if;
  dbms_output.put_line(';');


end;
/
spool off
set feedback on