[askdba] Re: Compare columns of 2 identical tables
- From: "Ankur Shah" <ankur_ora@xxxxxxxxxxx>
- To: <askdba@xxxxxxxxxxxxx>
- Date: Tue, 2 Nov 2004 16:18:30 -0500
We have a script which does similar exercise over db link between Prod and
Dev env......U can use it with bit modification like replace
your tablenames with ptablename and ptablename2 instead of the table using
dblink
=====================================
PROCEDURE COMPAREIT
( pTablename varchar2)
IS
vfield varchar2(3000);
BEGIN
for rec in (select * from col where tname=upper(pTablename)) loop
vfield := vfield || rec.cname||'||''~''||';
end loop;
vfield := substr(vfield,1,length(vfield)-2);
( ' select ''prod not dev'', '||ptablename||'.* from '||ptablename||'@prod
where
upper('||vfield||') in (
select
upper('||vfield||') dev from '||ptablename||'@prod
minus
select
upper('||vfield||') dev from '||ptablename||'@dev
)
union all
select ''dev not prod'', '||ptablename||'.* from '||ptablename||'@dev
where
upper('||vfield||') in (
select
upper(
'||vfield||') dev from '||ptablename||'@dev
minus
select
upper('||vfield||') dev from '||ptablename||'@prod
)
select ''demo not dev'', '||ptablename||'.* from '||ptablename||' where
upper('||vfield||') in (
select
upper('||vfield||') dev from '||ptablename||'
minus
select
upper('||vfield||') dev from '||ptablename||'@dev
)
union all
select ''dev not demo'', '||ptablename||'.* from '||ptablename||'@dev
where
upper('||vfield||') in (
select
upper('||vfield||') dev from '||ptablename||'@dev
minus
select
upper('||vfield||') dev from '||ptablename||'
) ');
END; -- Procedure
==============================================
HTHU
Ankur Shah
Oracle DBA
DHR-GA
----- Original Message -----
From: "Rajesh Puneyani" <rajpuneyani@xxxxxxxxx>
To: <askdba@xxxxxxxxxxxxx>
Sent: Tuesday, November 02, 2004 3:05 PM
Subject: [askdba] Compare columns of 2 identical tables
> Hi guys,
>
> I have 2 tables (Policy and Arch_policy). They would always be
> identical in structure.
>
> I have to write a procedure (PK column values to be passed in the
> procedure) to compare retrieved row from those tables. I have to
> compare column by column and report all those columns which differ in
> value.
>
> But the catch is it has to be dynamic ...i mean..the structure of the
> tables may change (new columns may be added, columns may be dropped)
> but still this procedure should keep reporting differences all the
> time.
>
> I hope I am clear.
>
> Can anyone please help me in designing such a procedure.
> Thanks
>
>
Other related posts:
