Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> table row "checksum"
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