Hash DataSet

Hashing :

1.  If the same DataSet is Passed to HashGenerator, we get the same Hash value out of it.

2. No two different Input Data Sets result in same Hash Value.


Based on above points, we can check if the process needs to be re-done.

Algorithm :

If HashOfTheProcessChanged then
    redo The Process;
else
    skip The Process;
end if;

           

Code :

 function generatehash (sqlstatement clob)  return varchar2 is

      xmlcontextHandle      DBMS_XMLGEN.CTXHANDLE;

      xml xmltype;

      xmlclob clob;

 begin

      xmlcontextHandle := dbms_xmlgen.newcontext (sqlstatement);

      begin

        xmlclob:=DBMS_XMLGEN.GETXML ( xmlcontextHandle);
      exception
        when others then
           -- close the context to release the resources & close the open cursors
          dbms_xmlgen.closecontext (xmlcontextHandle);
     end;

      dbms_xmlgen.closecontext (xmlcontextHandle);

      return (dbms_crypto.hash (xmlclob, 3));

 exception

    when others then   

         return (null);

 end generatehash;

Note : sqlstatement is the sql string (Can be Dynamic Sql)

          Be sure to use Order by Clause on the SQL Statement.

If Order by Clause not specified the order of the select fetch may change and it makes the hash change

Clob 1 :  (DataSet)

              1, kiran

              2, vishali

Clob 2 :  (DataSet)

              2, vishali

              1, kiran 

Hash on Clob 1 <> Hash on Clob 2

So the Sql Statement must specify the order by Clause to make sure we get the correct hash.

Example :

Select   generatehash  ( ' Select sysdate from dual' ) from dual;

Select   generatehash  ( ' Select ''1A2B3C'' from dual' ) from dual;

Declare 

  sqlStatement varchar2(100):= ' Select sysdate, ''1A2B3C'' from dual' ;

begin

  dbms_output.put_line(  generatehash (sqlStatement) );

end;