[askdba] Re: Compare columns of 2 identical tables
- From: Rajesh Puneyani <rajpuneyani@xxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Wed, 3 Nov 2004 09:42:32 -0500
Ankur,
Thank you very much. It really gave me a clear idea how to customize.
I appreciate.
Regards
On Tue, 2 Nov 2004 16:18:30 -0500, Ankur Shah <ankur_ora@xxxxxxxxxxx> wrote:
> 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:
