Re: SQL checksum function

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 20 Jan 2000 11:31:07 -0500
Message-ID: <nude8s4gq762qdslj719ur1ilfopa8r23e@4ax.com>

A copy of this was sent to "pmb" <pm_39_at_hotmail.com> (if that email address didn't require changing) On Thu, 20 Jan 2000 13:58:45 GMT, you wrote:

>I require a function in SQL which can generate a checksum from a given
>string.
>The function would return a value similar to the Unix 'cksum' command.
>
>Any help would be much appreciated,
>
>Paul

Here is one:

function checksum( p_buff in varchar2 ) return number is

    l_sum number default 0;
    l_n number;
begin

    for i in 1 .. trunc(length(p_buff||'x')/2) loop

        l_n := ascii(substr(p_buff||'x', 1+(i-1)*2, 1))*256 +
               ascii(substr(p_buff||'x', 2+(i-1)*2, 1));
        l_sum := mod(l_sum+l_n,4294967296);

    end loop;
    while ( l_sum > 65536 ) loop

        l_sum := bitand( l_sum, 65535 ) + trunc(l_sum/65536);     end loop;
    return l_sum;
end checksum;

I forgot where I got the algorithm from -- its a port of a C function that did a 32bit, 2's complement checksum.

It can be used in SQL statements and is also included in the OAS plsql web toolkit in the owa_opt_lock stuff (optimistic locking package)...

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jan 20 2000 - 10:31:07 CST