PL/SQL Script to Compare All Rows in Two Tables...

I have written the following script to compare all rows in two tables using three hashes. Its is quite useful. It can be extended to compare all tables in two databases...

DEFINE V_TABLE_1 = 'TABLE_ONE_NAME';
DEFINE V_TABLE_2 = 'TABLE_TWO_NAME';

SET SERVEROUTPUT ON
/
DECLARE

COLUMN_STRING VARCHAR(1000):='''START''';
HASH_TABLE_1 NUMBER;
HASH_TABLE_2 NUMBER;

BEGIN

FOR MINT IN
(
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS where table_name = '&V_TABLE_1'
)
LOOP
COLUMN_STRING := COLUMN_STRING ||'||'|| MINT.COLUMN_NAME;
END LOOP;

SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE(COLUMN_STRING,1,POWER(2,16)-1)) INTO HASH_TABLE_1 FROM &V_TABLE_1;
DBMS_OUTPUT.PUT_LINE('SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE('||COLUMN_STRING||',1,POWER(2,16)-1)) INTO HASH_TABLE_1 FROM &V_TABLE_1');
DBMS_OUTPUT.PUT_LINE('Hash Table 1-1 :'||HASH_TABLE_1);

SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE(COLUMN_STRING,1,POWER(2,16)-1)) INTO HASH_TABLE_2 FROM &V_TABLE_2;
DBMS_OUTPUT.PUT_LINE('SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE('||COLUMN_STRING||',1,POWER(2,16)-1)) INTO HASH_TABLE_2 FROM &V_TABLE_2');
DBMS_OUTPUT.PUT_LINE('Hash Table 2-1 :'||HASH_TABLE_2);

IF HASH_TABLE_1 = HASH_TABLE_2 THEN
DBMS_OUTPUT.PUT_LINE('TABLES MATCH');
ELSE
DBMS_OUTPUT.PUT_LINE('TABLES DO NOT MATCH');
END IF;

SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE(COLUMN_STRING,1,POWER(2,18)-1)) INTO HASH_TABLE_1 FROM &V_TABLE_1;
DBMS_OUTPUT.PUT_LINE('SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE('||COLUMN_STRING||',1,POWER(2,18)-1)) INTO HASH_TABLE_1 FROM &V_TABLE_1');
DBMS_OUTPUT.PUT_LINE('Hash Table 1-2 :'||HASH_TABLE_1);

SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE(COLUMN_STRING,1,POWER(2,18)-1)) INTO HASH_TABLE_2 FROM &V_TABLE_2;
DBMS_OUTPUT.PUT_LINE('SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE('||COLUMN_STRING||',1,POWER(2,18)-1)) INTO HASH_TABLE_2 FROM &V_TABLE_2');
DBMS_OUTPUT.PUT_LINE('Hash Table 2-2 :'||HASH_TABLE_2);

IF HASH_TABLE_1 = HASH_TABLE_2 THEN
DBMS_OUTPUT.PUT_LINE('TABLES MATCH');
ELSE
DBMS_OUTPUT.PUT_LINE('TABLES DO NOT MATCH');
END IF;

SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE(COLUMN_STRING,1,POWER(2,20)-1)) INTO HASH_TABLE_1 FROM &V_TABLE_1;
DBMS_OUTPUT.PUT_LINE('SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE('||COLUMN_STRING||',1,POWER(2,20)-1)) INTO HASH_TABLE_1 FROM &V_TABLE_1');
DBMS_OUTPUT.PUT_LINE('Hash Table 1-3 :'||HASH_TABLE_1);

SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE(COLUMN_STRING,1,POWER(2,20)-1)) INTO HASH_TABLE_2 FROM &V_TABLE_2;
DBMS_OUTPUT.PUT_LINE('SELECT SUM(DBMS_UTILITY.GET_HASH_VALUE('||COLUMN_STRING||',1,POWER(2,20)-1)) INTO HASH_TABLE_2 FROM &V_TABLE_2');
DBMS_OUTPUT.PUT_LINE('Hash Table 2-3 :'||HASH_TABLE_2);

IF HASH_TABLE_1 = HASH_TABLE_2 THEN
DBMS_OUTPUT.PUT_LINE('TABLES MATCH');
ELSE
DBMS_OUTPUT.PUT_LINE('TABLES DO NOT MATCH');
END IF;

END;
/