SQL & PL/SQL

Hi 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