Hi All,

I am new user, I have an issue.
I am using oracle 9i, it has the two instances, on one instance there are some table and the other instance there are table.
Let table 1 is from the instance one and table 2 is from instance 2. Now i have to make an compare utility against these two table and the compare will be on the column value (column value may be number, float, char, varchar and clob type) and it should be of exact match.
I want to log if there is some descripencies with all information.

If you have any queries, please let me know or you are founding that the example are not up to understanding i will modify in tabular form.

Thanks

Anand

Hi Anand,

As per my understanding to ur problem, this can be achieved by writing a procedure where u access the tables of both the instances and compare the column values.
I have just given a clue, probably u can make use of this & create ur own procedure.
For further queries, feel free to ask.

*** Mangai Varma ***

Hi Mangai,
Thanks for ur suggestion, i have the same idea but i am unable to write it in the code form. Could you please write a sample query?

Thanks

Anand

Hi Anand,
The following is a sample query which will compare the values from 2 tables.
I have used 2 cursors (cursor for loop) so that its simple & easily understandable. I have also used dbms output messages so that the process flow & the result can be viewed during execution.

SAMPLE CODE
===========
Create or replace procedure comp as
l_empno number(5);
l_new_empno number(5);
cursor l_emp_cur is
select empno from emp where sal > 3000 and deptno = 10;
cursor l_emp_new_cur is
select empno from emp_new where sal > 3000 and deptno = 10;
begin
for l_emp_rec in l_emp_cur
loop
dbms_output.put_line('after notfound if l_empno '||to_char(l_emp_rec.empno));
for l_emp_new_rec in l_emp_new_cur
loop
dbms_output.put_line('after notfound if l_new_empno '||to_char(l_emp_new_rec.empno));
if l_emp_rec.empno = l_emp_new_rec.empno then
dbms_output.put_line('same empno'||l_emp_rec.empno||' & '||l_emp_new_rec.empno);
else
dbms_output.put_line('different empno'||l_emp_rec.empno||' & '||l_emp_new_rec.empno);
end if;
if l_emp_new_cur%notfound then
exit;
end if;
end loop;
if l_emp_cur%notfound then
exit;
end if;
end loop;
end;
/

Using the above sample code, u can generate ur code with more enhancements whichever u require.
Hopefully its clear & understandable :-)

*** Mangai Varma ***