compare data between tables in test and production

[khushbubhalla]

how to compare data between tables and views row by row , column by column in the test and production environment

[jigs]

you can create a DB link from test to PROD.

do the minus of 2 tables to get the difference

[vijaydiwakar]

how to compare data between tables and views row by row , column by column in the test and production environment

pls explain in detail and give me thy tbl structure ,relations if any and some data from the tbls

[khushbubhalla]

you can create a DB link from test to PROD.

do the minus of 2 tables to get the difference

Hi
I dont want a generalised result , the minus query lists out al the differences.
here say if i have
table a (Test)
col1(Pk) col2(Businesskey) col3(Business key) col4 col5
1 ABC XYZ 1 2
2 BCA XXX 5 6
3 AAA YYY 3 3
4 EFG HIJ 4 5


table a (Prod)
col1(Pk) col2(Businesskey) col3(Business key) col4 col5
1 ABC XYZ 1 2
2 BCA YYY 5 6
3 AAA YYY 2 2
4 GFE JIH 7 8

if the structure looks some wat like this (My identifier are the business keys and not the Primary keys)
then i want the result dispalyed like this

Row existing in test and not in prod
4 EFG HIJ 4 5

Row existing in prod and not in test
4 GFE JIH 7 8

Rows Existing in both but not matching
2 BCA XXX 5 6 (Test)
2 BCA YYY 5 6 (Prod)
3 AAA YYY 3 3 (Test)
3 AAA YYY 2 2 (Prod)

i want to write a generic procedure for the same which can give me these results. the input of the procedure will be the table names , columns names and the Db link

Can u pls help me with this

[khushbubhalla]

pls explain in detail and give me thy tbl structure ,relations if any and some data from the tbls

Hi
I dont want a generalised result , the minus query lists out al the differences.
here say if i have
table a (Test)
col1(Pk) col2(Businesskey) col3(Business key) col4 col5
1 ABC XYZ 1 2
2 BCA XXX 5 6
3 AAA YYY 3 3
4 EFG HIJ 4 5


table a (Prod)
col1(Pk) col2(Businesskey) col3(Business key) col4 col5
1 ABC XYZ 1 2
2 BCA YYY 5 6
3 AAA YYY 2 2
4 GFE JIH 7 8

if the structure looks some wat like this (My identifier are the business keys and not the Primary keys)
then i want the result dispalyed like this

Row existing in test and not in prod
4 EFG HIJ 4 5

Row existing in prod and not in test
4 GFE JIH 7 8

Rows Existing in both but not matching
2 BCA XXX 5 6 (Test)
2 BCA YYY 5 6 (Prod)
3 AAA YYY 3 3 (Test)
3 AAA YYY 2 2 (Prod)

i want to write a generic procedure for the same which can give me these results. the input of the procedure will be the table names , columns names and the Db link

CAn u help me with this

[vijaydiwakar]

Hi
I dont want a generalised result , the minus query lists out al the differences.
here say if i have
table a (Test)
col1(Pk) col2(Businesskey) col3(Business key) col4 col5
1 ABC XYZ 1 2
2 BCA XXX 5 6
3 AAA YYY 3 3
4 EFG HIJ 4 5


table a (Prod)
col1(Pk) col2(Businesskey) col3(Business key) col4 col5
1 ABC XYZ 1 2
2 BCA YYY 5 6
3 AAA YYY 2 2
4 GFE JIH 7 8

if the structure looks some wat like this (My identifier are the business keys and not the Primary keys)
then i want the result dispalyed like this

Row existing in test and not in prod
4 EFG HIJ 4 5

Row existing in prod and not in test
4 GFE JIH 7 8

Rows Existing in both but not matching
2 BCA XXX 5 6 (Test)
2 BCA YYY 5 6 (Prod)
3 AAA YYY 3 3 (Test)
3 AAA YYY 2 2 (Prod)

i want to write a generic procedure for the same which can give me these results. the input of the procedure will be the table names , columns names and the Db link

CAn u help me with this

select * from table1 t1 ,table2 t2
where t1.col2<>t2.col2 and t1.col3<>t2.col3

and so on add ur columns here

[khushbubhalla]

select * from table1 t1 ,table2 t2
where t1.col2<>t2.col2 and t1.col3<>t2.col3

and so on add ur columns here

The query that u have given gives me a very generic result.
I want a procedure which takes the table name and the column names as input
and the out put is more like a report from where i can identify wat is the problem
If i list all the columns in the query it will give me the entire record set
this doesn solve my purpose

[amr]

select col4,col5
from test
minus
select col4,col5
from prod;

[khushbubhalla]

i want to make a procedure which takes the tablename,columnnames as input.
from the system tables we somehow find the unique index on the table and then have generic joins on the same
i am not quite sure as to how will i come up with such a generic procedure.
I want a specific result set.

[khushbubhalla]

Just An algo of wat i want to do and am not really sure how
Proc(tablename(to be compared) , columnnames(that need to be compared), Business keys (or saythe unique indexeshere its col2 and col3))
1) Do a record matching analysis
Find the no of records matching based on Business keys
that ll be done by putting a join some wat like this
select l.col2,l.col3,r.col2,r.col3,'Test' as TableName from testa l, proda r where l.col2 = r.col2 and l.col3 = r.col3 union
select l.col2,l.col3,r.col2,r.col3,'Prod' as TableName from testa l, proda r where l.col2 = r.col2 and l.col3 = r.col3

2) & 3) the records present in one prod not in test and vice versa can be found out using
select col2,col3,'Test' as TABLENAME from ((select col2,col3 from testa minus (select l.col2,l.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3)))
UNION
select col2,col3,'Prod' as TABLENAME from((select col2,col3 from proda minus (select r.col2,r.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3)))

4)find the records present in both but different
select col2,col3,'Test' as TABLENAME from
((select l.col2,l.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3) minus
(select l.col2,l.col3 from testa l, Proda r where l.col2=r.col2 and l.col3=r.col3))
union
select col2,col3,'Prod' as TABLENAME from
((select r.col2,r.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3) minus
(select r.col2,r.col3 from testa l, Proda r where l.col2=r.col2 and l.col3=r.col3))

after this i want to perform an analysis of the matching records on the non keys
that can be done by a join on all attributes.

end of algo

i want the entire thing to be generic , so that it can be used for any table
the no of columns and the business keys wil vary with every time
i want a generic way of taking the column names and the business keys as a string from user and put them in the queries in a very generic way

[Farid Z]

Another option is to use a database data comparison tool such as Zidsoft CompareData

[amitpatel66]

how to compare data between tables and views row by row , column by column in the test and production environment

The SET operations should really help you out:
Try this in test environment.

declare
tab_name VARCHAR2(20);
col_name VARCHAR2(20);
emp_det emp%ROWTYPE;
BEGIN
tab_name:= &1;
col_name:= &2;
 
EXECUTE IMMEDIATE 'SELECT '|| col_name|| ' FROM '|| tab_name INTERSECT 'SELECT '|| col_name|| ' FROM '|| tab_name||'@db_link' BULK COLLECT INTO emp_det;
END;
 

The above code will get you similra records from two tables of test and production instances. Do it similarly for getting uncommon rows.