CDC using sql and pl/sql
548617Jul 21 2009 — edited Jul 22 2009Hi All,
I am in a project where I have to implement Change Data capture using sql and pl/sql to migrate 1year of history data from one data warehouse to the new one. This is a one time effort.
CDC - Change data capture means. If a field in a particular record gets updated, instead of updating the record, we end date the record with the current date and insert a new record with the modified value and an end date of future date.
I understand it can be better implemented using Informatica or some other ETL tools, but I have been asked to do it in SQL and PL/SQL.
my approach to do a CDC on table A is as follows.
1. Load the file with all the new records into a temp table.
2. Do a minus of keys on this temp table and table A, which will give us new records, to be inserted into table A. -easy
3. Do intersect of keys on table A and temp table, which will give us the list of records to be checked for updates.
But I am not able to find a way to check if the records from step 3 have undergone any change, and if yes, how to end date the earlier record and insert a new record.
Any ideas to crack this are greatly appreciated.
One of my friend said in Informatica they have a way to concatenate all the fields in the table and then do a minus operation. Do we have any such thing in Oracle. As far as i know, we don't have any.
Thanks
Hari