Tuesday 28 August 2007

Oracle - table changes how to notice

Couple of days ago I had a real problem how to provide always the most recent report with the products hierarchy. The first thought was to write a kind of a hash function to get just a hash stamp of the whole table and compare against the new one. The problem arose when it came to implementing the idea.
There is no built way to get a hash value of a whole table.

Suddenly I thought: "OK, let's check if the number of rows is different, then it would mean the content of the table changed".

I walked that way.
Replacing the table I always create first the new one with some different name and if everything goes fine, I replace the name to the right one at the end.

I have a production table called prods_hier and the new one in the background called prods_hier_bak

the following lines of code do the comparing:

changed:=false
select count(*) into no_of_rows_new from prods_hier;
select count(*) into no_of_rows_bak from prods_hier_bak;
if no_of_rows_new <> no_of_rows_bak then
changed := true;
else -- the same no_of_rows
select count(*) into no_of_changed_rows from
( select * from prods_hier
minus
select * from prods_hier_bak
);
if no_of_changed_rows <> 0 then
changed := true;
end if;
end if;

No comments: