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

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...

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...

"saurangshu" <saurangshu@gmail.com> wrote in message
news:1133355228.689033.171600@z14g2000cwz.googlegr oups.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 ld values in a row trigger and pass them to the
procedure.
Jim

I don't have a trigger on the table as I don't own them and we can't
change the called API signature. Hence, we couldn't use this.

Thanks for the reply.

On 30 Nov 2005 07:25:55 -0800, "saurangshu" <saurangshu@gmail.com>
wrote:

>I don't have a trigger on the table as I don't own them and we can't
>change the called API signature. Hence, we couldn't use this.
>
>Thanks for the reply.

Triggers don't need to be owned by the table owner.

--
Sybrand Bakker, Senior Oracle DBA

True. But, in my case the triggers on tables are not there and the
people are not ready to provide it due to various performance reasons.
Any comments on the current approach particularly from a performance &
scalability stand point?

Any other alternate idea if any?

thanks,

--saurangshu

On 30 Nov 2005 10:30:17 -0800, "saurangshu" <saurangshu@gmail.com>
wrote:

>True. But, in my case the triggers on tables are not there and the
>people are not ready to provide it due to various performance reasons.
>Any comments on the current approach particularly from a performance &
>scalability stand point?
>
>Any other alternate idea if any?
>
>thanks,
>
>--saurangshu

So far, you have never provided anything but political cover your ass
answers why a viable approach wasn't possible.
As you are evidently not trying to address the political reasons, the
only choice you have if to learn to live with it, and to get in
writing why you can't change, in order to slam your management in the
face with their signatures, once the app becomes unscalable (which it
obviously will, as you are, if I'm understanding you correctly,
executing fake transactions to get what you want).

--
Sybrand Bakker, Senior Oracle DBA

What if two other users change the value of X to Z and W?

jg
--
@home.com is bogus.
"What is that knot in your stomach trying to tell you?
" - part of my horoscope for today.

"Joel Garry" <joel-garry@home.com> wrote:
> What if two other users change the value of X to Z and W?

Then you are not using Oracle.

The "outer" transaction locks the row when it performs the initial update.
Other users trying to update that row will be blocked.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

xhoster@gmail.com wrote:
> "Joel Garry" <joel-garry@home.com> wrote:
> > What if two other users change the value of X to Z and W?
>
> Then you are not using Oracle.
>
> The "outer" transaction locks the row when it performs the initial update.
> Other users trying to update that row will be blocked.

Shoot, you are right. I've been corrupted by db-blind apps.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniont...1b1patent.html

Sybrand,

It is not at all correct that I have been playing as a 'political' game
to devise this problem. This is the fact that you can't add triggers or
changes existing PL/SQL api that is getting called. And, the reason for
this is that we are customizing oracle e-business suite and they will
*NOT* support a trigger kind of customization on their base table due
to performance reason. This is the everyday fact & pain for all the
product customization personnel and they have to live with it. If you
have worked on such things you will know otherwise not.
My intent of the post was to judge the merit and demerit of my approach
and to find out a better approach if available (with some solid backing
of facts) in my domain of constraints and it was not to defy 'viable
approaches'.

I will not touch rest of the comment.

saurangshu wrote:
> Sybrand,
>
> It is not at all correct that I have been playing as a 'political' game
> to devise this problem. This is the fact that you can't add triggers or
> changes existing PL/SQL api that is getting called. And, the reason for
> this is that we are customizing oracle e-business suite and they will
> *NOT* support a trigger kind of customization on their base table due
> to performance reason. This is the everyday fact & pain for all the
> product customization personnel and they have to live with it. If you
> have worked on such things you will know otherwise not.
> My intent of the post was to judge the merit and demerit of my approach
> and to find out a better approach if available (with some solid backing
> of facts) in my domain of constraints and it was not to defy 'viable
> approaches'.
>
> I will not touch rest of the comment.
>
Oh my god - do you think any oracle eBusiness configuration
will actually perform?!?
Of course not - the got the many end on the wrong side (or you have )
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

I know