Re: Regarding reading records before a transaction started

"saurangshu" <saurangshu_at_gmail.com> wrote in message news:1133355228.689033.171600_at_z14g2000cwz.googlegroups.com...
> 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...
>

I use the :new and :old values in a row trigger and pass them to the procedure.
Jim Received on Wed Nov 30 2005 - 09:09:04 CST