Home » SQL & PL/SQL » SQL & PL/SQL » How to send only delta records from table to flat file?
How to send only delta records from table to flat file? [message #210438] Wed, 20 December 2006 18:03 Go to next message
vishy55
Messages: 1
Registered: December 2006
Junior Member
We are doing integration with a legacy system and need to send only the delta data insert/update once in 24 hours. I'll ask you with example. e.g:I have a training table which has 2 columns 1.emplid and 2.training taken. I already have one record for my emplid and say the training taken is 'peoplesoft'. Today I take one more training say 'oracle' and I want to send only this record because this is my delta record. How can I acheive this? One way to do is write a trigger on insert/update and then store the delta records onto a staging table with a date field.

Is there any other way to implement this? Please explain with example. Do you know if snapshot is another better option? I have never worked on snapshots so please let me know with a example.
Re: How to send only delta records from table to flat file? [message #210444 is a reply to message #210438] Wed, 20 December 2006 19:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
In oracle 9i it is called CDC (Change data capture).
In oracle 10g it is STREAMS.
These techniques appear to be hard to impliment. But it is not.
Writing trigges is old fashioned and practically not a good solution if there are too many tables.
Just search google/oracle documentation.
Re: How to send only delta records from table to flat file? [message #210656 is a reply to message #210444] Thu, 21 December 2006 12:22 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
A simple materialized view (snapshot) with the appropriate refresh interval may be an acceptable solution for you. I'd say it's the easiest solution - see the documentation.
Re: How to send only delta records from table to flat file? [message #211203 is a reply to message #210438] Wed, 27 December 2006 03:20 Go to previous message
Arun Srinath
Messages: 12
Registered: January 2005
Junior Member
You could have 4 additional columns in your table.

created_by (to be populated when the record is freshly created)
created_on (to be populated when the record is freshly created)
last_updated_by (to be populated when the record is updated)
last_updated_on(to be populated when the record is updated)


your program logic which populates this table should automatically update these 4 columns whenever any operation is performed in the table. These columns are called "who" columns.
These columns also helps in auditing purposes.

Once these 4 columns are present for all the records, you can have a cutom pl/sql program which looks for these columns to identify the recently updated or inserted records and then insert them into staging table.

Hope this helps

Thanks
Arun
Previous Topic: function with varray to take out average of students
Next Topic: how to dynamically access members of record structure
Goto Forum:
  


Current Time: Fri Dec 09 11:49:11 CST 2016

Total time taken to generate the page: 0.08710 seconds