hi,
I have a table in oracle 7.3. from which i have generate XML file.
The problem is, the windows service has to pick up the last inserted records only. Window service is accessing the database every time the timer elapsed. tell me the way to get the last inserted record without identity colum?

Unless you are storing some sort of identifier that tells you what is "new", you cannot get just the most recent data.

Potentially, you could add a trigger to the table that would insert a row into a transaction table. The service could then pull data from that transaction table, apply the changes, and delete the change record. In more recent versions of Oracle, there are a variety of tools for tracking data changes that will be simpler and more performant than this trigger based approach.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

thanks for your reply.

can u explian your idea in detail.

thanks in advance.

If you have a table t1, you could create a table that stored the changes to t1, i.e. t1_change.

You could then create a trigger on t1 that wrote data to t1_change. Exactly what you would need to store would depend on the structure of t1, but it would presumably be something that uniquely identified the row. The primary key would be preferrable, though, you could use ROWID as well. The precise needs of the extract process will matter here as well. How are DELETE's handled? Do you need to extract the complete "before" image of the deleted row? What about for UPDATE's? Do you need to know the old data or just the new data? How are multiple changes to the same row handled? If I INSERT, UPDATE, and then DELETE a row between extracts, do you need to note all those changes or just the final status?

The extract process would read from t1_change, potentially do a lookup for the new data in t1, extract the data, and delete the row from t1_change.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC