But note what happens for record R1. On Day 2, the hash value changed (from 100 to 200) due to a different value (V4) in Field 3 - so the new record got inserted with hash value 200. The new record gets flagged Y and the previous one gets tagged N. But on Day 3, when the Field 3 of R1 changes back to V3 (from V4), it's hash value is again back to 100. Now, since the logic checks that if the new hash value (100) is different from the existing value (200) of the existing R1 record tagged Y, it will flag the record with hash value 200 as N. Now, I have to be careful to make sure the old hash value 100 which already resides with a flag of N do not interfere with the new record (also having hash value 100).
Several scenarios can happen in this case if not handled properly:
1. We can have duplicates of the record (with hash value 100) both flagged as N since the hash value is already existing
2. We can have duplicates of the record (with hash value 100) both flagged as Y since the hash value is already existing and it's also the latest
3. The new record with old hash value 100 might not get inserted at all (most popular error) since hash value 100 is already existing - thus the full set of R1 stays tagged as N with no Y
4. The new record with old hash value 100 can get inserted but subsequently can get tagged N as the hash value is old - thus the full set of R1 stays tagged as N with no Y
All of these can be overcome with some additional logic to handle them. It's not that we cannot use ORA_HASH to implement SCD2, in fact it is very handy, just that little more caution is required to cover all the scenarios. Where do you like to use ORA_HASH in your codes?