Re: Regarding reading records before a transaction started

May be I should elaborate a bit more about what I am doing. As mentioned earlier, I have the table T1 with row R1 having a column say C1 pointing to an entity in some other table. Now at the beginning of the transaction, some procedure say P1 mutates the value of C1 from value say X to Y. Now, this procedure P1 in turn calls my procedure P2 which wants to know the older value of column C1 (i.e. 'X') to process some transactions. But, if I try to fire an sql like 'select C1 from T1' in my procedure P2, then I will always get 'Y' and not 'X". My problem is to get the value of the column before the current transaction was started.

Please let me know if I was not clear.

Thanks.
--Saurangshu

Frank van Bortel wrote:
> saurangshu wrote:
> > 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
> >
>
> You don't need an autonomous transaction for reads.
> And, depending on the way you update, why isn't the
> old value known withing P0?
>
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...
Received on Wed Nov 30 2005 - 06:53:48 CST