Results 1 to 4 of 4

Thread: Compare utility against two table

  1. #1
    Junior Member
    Join Date
    Feb 2007
    Answers
    3

    Compare utility against two table

    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


  2. #2
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: Compare utility against two table

    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 ***


  3. #3
    Junior Member
    Join Date
    Feb 2007
    Answers
    3

    Re: Compare utility against two table

    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


  4. #4
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: Compare utility against two table

    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 ***

    Last edited by Innila; 02-19-2007 at 01:53 AM. Reason: Formatting purpose

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact