Row comparision for 2 identical tables - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Row comparision for 2 identical tables

  1. #11
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    To check if they are equal, isn't there a function that calculates a hash value of the whole row? (never used it)

    For the output, how about:
    REPLACE(tab1.col1, tab2.col1)||REPLACE(tab2.col1, tab1.col1) etc etc
    gives you NULL if identical and something to work with if they are not (I'm assuming a quick & dirty is OK).

  2. #12
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    function that calculates a hash value of the whole row
    Can't find any reference to that on metalink. Sounds useful. Anybody know what this is?

  3. #13
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Later I got as far as finding this: http://www.oracle-base.com/articles/...gMethods9i.php
    look under "Checksum" - however
    increases the amount of code necessary since the CHECKSUM function cannot be used as part of the SQL statement.
    . . but I couldn't track down "owa_opt_lock.checksum" . . . good hunting!

  4. #14
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    owa_opt_lock.checksum function :
    ===================================

    Syntax
    owa_opt_lock.checksum(p_buff in varchar2) return number;
    owa_opt_lock.checksum(
    p_owner in varchar2
    p_tname in varchar2
    p_rowid in rowid) return number;

    Purpose :
    -------------
    This function returns a checksum value for a specified string, or for a row in a table. For a row in a table, the function calculates the checksum value based on the values of the columns in the row. This function comes in two versions.

    The first version returns a checksum based on the specified string. This is a "pure" 32-bit checksum executed by the database and based on the Internet 1 protocol.

    The second version returns a checksum based on the values of a row in a table. This is a "impure" 32-bit checksum based on the Internet 1 protocol.

    Parameters :
    --------------
    p_buff - the string for which you want to calculate the checksum.

    p_owner - the owner of the table.

    p_tname - the table name.

    p_rowid - the row in p_tname for which you want to calculate the checksum value. You can use the owa_opt_lock.get_rowid function to convert vcArray values to proper rowids.

    Return Value :
    ----------------
    A checksum value.

  5. #15
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Had a go at dinnertime yesterday to compare two tables. This is the code so far, it currently relies on primary key match between
    the two tables (col1), and needs some work still. It uses owa_opt_lock.checksum. Not considered the output of erroneous data as yet.

    Yes, some of the variables could be amalgamated into single select statements, but for clarity, I've used variables
    for each stage of the process. Change col4 in any table to cause a mismatch.

    It's a start..........

    Code:
    >> desc horace1
     Name          Type
     ------  -----------
     COL1      NUMBER(2)
     COL2      NUMBER(2)
     COL3      NUMBER(2)
     COL4      VARCHAR2(2)
    
    >> desc horace2
     Name          Type
     ------  -----------
     COL1      NUMBER(2)
     COL2      NUMBER(2)
     COL3      NUMBER(2)
     COL4      VARCHAR2(2)
    
    
    >> select * from horace1;
    
         COL1      COL2      COL3 COL4
    --------- --------- --------- --
            1         2         3 AB
            2         2         3 AB
            3         2         3 AB
    
    >> select * from horace2;
    
         COL1      COL2      COL3 COL4
    --------- --------- --------- --
            1         2         3 AB
            2         2         3 AB
            3         2         3 AB
    
    
    DECLARE
    
        v_rowid1  rowid; /* stores rowid from first table */
        v_rowid2  rowid; /* stores rowid from second table */
        
        checksum1 number; /* stores checksum for rowid from first table */
        checksum2 number; /* stores checksum for rowid from second table */
    
    
        cursor CUR_TABLE1 is select H1.rowid,H1.col1      /* grabs rowid and primary key(col1) from first table */
                             from horace1 H1,horace2 H2   /* where primary key matches second table             */
                             where H1.col1 = H2.col1;  
    
        different_content   EXCEPTION;   /* exception to handle when tables have different contents */
    
        
           
    BEGIN
    
      FOR REC_TABLE1 in CUR_TABLE1
    
        LOOP
    
        v_rowid1 := REC_TABLE1.rowid;  /* store rowid from first table */
    
        select rowid into v_rowid2
        from HORACE2
        where col1 = REC_TABLE1.col1;  /* store rowid from second table where primary key matches first table */
    
        checksum1 := owa_opt_lock.checksum('WAREHOUSE_PROD','HORACE1', v_rowid1); /* calculate checksum for row in table 1 */
        checksum2 := owa_opt_lock.checksum('WAREHOUSE_PROD','HORACE2', v_rowid2); /* calculate checksum for row in table 2 */
    
     --   DBMS_OUTPUT.PUT_LINE(checksum1); /* test and debug */
     --   DBMS_OUTPUT.PUT_LINE(checksum2); /* test and debug */
    
           IF checksum1 = checksum2 /* compare checksums */
    
           THEN
    
             null;  /* if checksums match, do nothing */
    
           ELSE
    
             raise DIFFERENT_CONTENT;  /* if checksums do not match, raise exception */
    
           END IF;
    
        END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Tables match');
    
      EXCEPTION 
    
        WHEN DIFFERENT_CONTENT
        THEN     DBMS_OUTPUT.PUT_LINE('Tables do not match');
        
        WHEN OTHERS
        THEN DBMS_OUTPUT.PUT_LINE('Something else is wrong');
    
    END;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width