ORA_HASH to compare two tables/(sub)partitions

When you suggest a new method to move the data from one db to another db (previous post) , you should prove that every row is migrated successfully.
There are lots of examples for ORA_HASH implementation on the net. This is yet another one:
The sp uses listagg and ora_hash together.
The input may include the owner,table_name,partition or subpartition.

create or replace procedure GET_ORA_HASH_TABLE
        (owner in varchar2, table_name in varchar2,partition_name in varchar2 default NULL,sub_partition_name in varchar2 default NULL,hash_value out varchar2 ) is
        l_all_columns varchar2(4000);
        v_dyntask   varchar2(20000);
        CURSOR get_columns(p_owner varchar2,p_table_name varchar2) IS
            select listagg(column_name,'||') WITHIN GROUP (order by column_id) all_columns from dba_tab_columns where owner=p_owner and table_name=p_table_name;
BEGIN
   open get_columns(owner,table_name);
   fetch get_columns into l_all_columns;
   close get_columns;
   v_dyntask := 'select sum(ora_hash('|| l_all_columns ||'))  from '|| owner ||'.'|| table_name ;
   if sub_partition_name is  NOT NULL
   THEN
            v_dyntask := v_dyntask || ' subpartition ('|| sub_partition_name ||')';
   else if partition_name is  NOT NULL
            THEN
                                     v_dyntask := v_dyntask || ' partition ('|| partition_name ||')';
         end if;
  end if;
   execute immediate v_dyntask into hash_value ;
END;

Usage on subpartitions :

SQL> SET SERVEROUTPUT ON;
SQL> declare
  2  hash_value varchar2(4000);
  3  BEGIN
  4      GET_ORA_HASH_TABLE('OWNER','TABLE_NAME','','SP2011JAN01_01',hash_value);
  5      DBMS_OUTPUT.PUT_LINE(hash_value);
  6  END;
  7  /
43437576967369636

PL/SQL procedure successfully completed.

And on partitions:

SQL> r
  1  declare
  2  hash_value varchar2(4000);
  3  BEGIN
  4      GET_ORA_HASH_TABLE('OWNER','TABLE_NAME','P2011JAN01','',hash_value);
  5      DBMS_OUTPUT.PUT_LINE(hash_value);
  6* END;
695708730528399811

PL/SQL procedure successfully completed.

SQL> 

And on table:

 1  declare
  2  hash_value varchar2(4000);
  3  BEGIN
  4      GET_ORA_HASH_TABLE('OWNER','TABLE_NAME','','',hash_value);
  5      DBMS_OUTPUT.PUT_LINE(hash_value);
  6* END;

About these ads