Regarding reading records before a transaction started

Hi Oracle Gurus,

I had a bit peculiar requirement where I need to get the records/values for a table before the current transaction was started. The situation is as depicted below -

I have got a table say T1 with a row say R1 at the beginning of the transaction. Now, our application calls a PL/SQL procedure, which mutates the row R1 and then in turn calls my procedure P1 in the same transaction. Inside the procedure P1, I need to know the original value of the mutated row R1 inside this current transaction context and I don't have a chance to change our API to pass this information from the caller procedure.

I solved the problem by calling a function from my PL/SQL procedure, which starts an autonomous transaction and read the value of R1as it was before the transaction. This strategy works fine for me. Now, I want to validate whether there is better way of doing this thing rather than my approach? And, if there is any pitfall of my approach regarding performance and scaling of the procedure.

I am using Oracle 10gR2 database for this.

Please post you valuable comments/insight on this approach.

Thanks in advance,

Saurangshu Received on Wed Nov 30 2005 - 03:00:55 CST