I have 2 tables which of same structure with around 180 columns in each table with one columns as PK. Each table have around 200 k records. I need to compare to tables columns by column and if for that records any difference is found for any of the remaining 179 columns then need to track that column name and the both the old values and new value from that column from both tables.
This can be achieve by SQL statement with UNION and group and LEAD functions but as need to compare the for 179 fields the length of the SQL code is very long. So thought of used nested table collection to compare both the tables by bulk collect both the values is 2 different nested tables and iterate them. First loop to iterate using collection count value and second loop using USER_TAB_COLS to iterate based on number of columns in the tables. Is there any possible to pass the field name to the nested loop dynamically ? Below is the sample code for that.
SET SERVEROUTPUT ON;
DECLARE
TYPE TEST1_TYPE IS TABLE OF TEST1%ROWTYPE ;
TEST1_TAB TEST1_TYPE;
TEST2_TAB TEST1_TYPE;
lcCol1 VARCHAR2(3000);
lcCol2 VARCHAR2(3000);
lQuery VARCHAR2(3000);
CURSOR CUR_TAB_COL IS
SELECT
COLUMN_NAME ,DATA_TYPE
FROM USER_TAB_COLS
WHERE TABLE_NAME='TEST1'
ORDER BY COLUMN_ID;
TYPE COL_TYPE IS TABLE OF CUR_TAB_COL%ROWTYPE;
COL_TAB COL_TYPE;
BEGIN
SELECT
*
BULK COLLECT INTO TEST1_TAB
FROM TEST1
ORDER BY ID;
SELECT
*
BULK COLLECT INTO TEST2_TAB
FROM TEST2
ORDER BY ID;
OPEN CUR_TAB_COL;
FETCH CUR_TAB_COL BULK COLLECT INTO COL_TAB;
CLOSE CUR_TAB_COL;
FOR I IN 1..TEST2_TAB.count
LOOP
FOR j IN COL_TAB.FIRST..COL_TAB.LAST
LOOP
lQuery:='SELECT TEST1_TAB('||i||').'||COL_TAB(j).COLUMN_NAME||',FROM DUAL';
EXECUTE IMMEDIATE lQuery INTO lcCol1;
lQuery:='SELECT TEST2_TAB('||i||').'||COL_TAB(j).COLUMN_NAME||',FROM DUAL';
EXECUTE IMMEDIATE lQuery INTO lcCol2;
END LOOP;
END LOOP;
END;
/
I would stick to a pure SQL solution. Nested loops through the arrays will get slow as the data volumes grow. Plus you may run out of memory trying to bulk collect the whole table into an array.
This question covers various ways of doing this:
https://asktom.oracle.com/pls/apex/asktom.search?tag=how-to-compare-two-tables-of-data If you just want to save yourself some typing, Stew Ashton has built a package to generate the SQL for you:
https://stewashton.wordpress.com/2015/01/21/stew_compare_sync-introducing-the-package/ To compare column-by-column, unpivot the tables first:
create table tnew ( pk primary key, c1, c2 ) as
select level pk, mod ( level, 2 ) c1, level c2 from dual
connect by level <= 10;
create table told ( pk primary key, c1, c2 ) as
select level pk, mod ( level, 4 ) c1, level c2 from dual
connect by level <= 10;
select pk, col, val,
sum(old_ct) old_ct, sum(new_ct) new_ct
from (
select t.*, 1 old_ct, 0 new_ct
from told
unpivot (
val for col in ( c1, c2 ) -- list of columns to compare
) t
union all
select t.*, 0 old_ct, 1 new_ct
from tnew
unpivot (
val for col in ( c1, c2 )
) t
)
group by pk, col, val
having sum(old_ct) != sum(new_ct)
order by 1, new_ct;
PK COL VAL OLD_CT NEW_CT
2 C1 2 1 0
2 C1 0 0 1
3 C1 3 1 0
3 C1 1 0 1
6 C1 2 1 0
6 C1 0 0 1
7 C1 3 1 0
7 C1 1 0 1
10 C1 2 1 0
10 C1 0 0 1Again, if you want to save yourself some typing/make this dynamic, you can use listagg to generate the column list to put in the unpivot clause for each table:
select listagg ( column_name, ',' )
within group ( order by column_id ) cols
from user_tab_cols
where table_name = 'TNEW'
and column_name not in ( -- assumes table as PK
select ucc.column_name from user_constraints uc
join user_cons_columns ucc
using ( constraint_name )
where uc.table_name = 'TNEW'
and uc.constraint_type = 'P'
);
COLS
C1,C2