Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: table row "checksum"

Re: table row "checksum"

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Thu, 5 Jan 2006 20:32:38 -0800
Message-ID: <36idna5p843pbyDeRVn-iA@comcast.com>

"Geoff Muldoon" <geoff.muldoon_at_trap.gmail.com> wrote in message news:MPG.1e287639dc47e87989740_at_news.readfreenews.net...
> Version 9.2.x about to upgrade to v.10 on Linux, data warehouse
> implementation ....
>
> Seeking advice as to the best method of generating a "table row checksum".
>
> In brief:
> Several dozen dimension tables with varying numbers of columns, mix of
> varchar2, number and date data types. Each night the data warehouse gets
> an end-of-day snapshot from OLTP system, values are checked against
> previous end-of-day values for keys in each table, when changes detected,
> old row in datestamped inactive and new row for that key is inserted.
>
> Rather than select all old values for the key and individually check them
> against the possible new values, I want to store a "row checksum" of the
> concatenated values in another column in each table, then call a function
> passing the concatenated potential new values and see if the checksums
> match.
>
> Have been considering:
> CREATE function GET_CHECKSUM(v_input in varchar2) return number is
> v_output number;
> begin
> v_output := UTL_RAW.CAST_TO_NUMBER(UTL_RAW.CAST_TO_RAW(v_input));
> return v_output;
> end;
>
> where the input string is c1||'_'||c2||'_'||c3||......
> (concat of the underscore between each column is to cope with "shifting
> nulls", so 'x', null, 'y' won't equal 'x', 'y', null).
>
> Would greatly appreciate advice as to whether there is an easier, more
> robust or more sensible way of achieving this.
>
> Geoff M

you can use the built in Oracle functionality to generate a hash. (See Tom Kyte's latest book). I don't have it in front of me but look at the documentation for the md4,5 or sh something or other check sums (hash) Jim Received on Thu Jan 05 2006 - 22:32:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US