How to load a Slowly Changing Dimension Type 2 with one SQL Merge statement in Oracle

This is based on Design Tip 107 ("Using the SQL MERGE Statement for Slowly Changing Dimension Processing") and does sth. similar in Oracle

You can access the solution at http://www.business-intelligence-quotient.com/?p=66

Uli,
Thanks for working through this and making it available to the group. I tried to do it on Oracle for the design tip, but my local copy of Oracle is 10g. Even with 11, the need to identify new versus changed rows in the extract process may be an issue more often than one would think. Many of the organizations we see do not have the mechanisms in place to support this kind of change data capture. In any case, it looks like it's time for me to upgrade.
--Warren

p.s. I'm going to ask the moderator to move this to the ETL topic since it's more about implementation than modeling.

Warren,

this should work on Oracle 10G also. It is also my experience that more often than not the information on changed or new records is not available from source. This is also the case for the project I work on currently. In the main source system they just have an activity_date that acts as a catch all for both updates and inserts...

Hello all. I have made an Oracle code generator for maintaining an SCD. It is written as a stored procedure in PL/SQL, it generates and executes the PL/SQL code on-the-fly, without creating any schema object (stored procedures etc). It uses bulk processing and takes following parameters as input:
1. source table/view name - actually a whole SELECT could be provided as well I think (although I didn't try).
2. target table name
3. natural key (comma-separated list of column names)
4. name of surrogate key column in target table
5. comma-separated list of columns treated as type 1 (overwritten on change)
6. comma-separated list of columns treated as type 2 (versioned attributes, new row added, previous closed)
7. Oracle sequence name used to assign surrogate keys
8. names of "date-from", "date-to" target table columns
9. name of "current-record-flag" target table column.
It returns number of new values (actually new), number of overwritten records and number of versioned records.
The procedure has 220+ lines of code, so I shall not paste it here, but I can send it to anyone interested.
Regards,
Jacek Adamowicz