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 -> table row "checksum"

table row "checksum"

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Fri, 6 Jan 2006 12:43:12 +1100
Message-ID: <MPG.1e287639dc47e87989740@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 Received on Thu Jan 05 2006 - 19:43:12 CST

Original text of this message

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