Re: trying to understand transaction control in pl/sql

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 29 Oct 2004 18:31:33 -0700
Message-ID: <1099099832.295079@yasure>

ken quirici wrote:

> DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1098932765.849260_at_yasure>...
>

>>Manfred Peter wrote:
>>
>>
>>>But this is not a good programing style as somebody could call your 
>>>procedure or function and the
>>>caller can not decide wether to commit the transaction or to rollback it.
>>
>>Your advice confuses me completely. Do you understand Oracle
>>architecture?
>>
>>Of what possible relevance is there to another session executing any
>>code anywhere in the database?

>
>
> Hi Daniel,
>
> I took the advice to be: avoid putting commits into called functions
> or procedures because (unless they're declared as autonomous
> transactions) they force the entire process to commit, which the
> calling process may want to decide for itself.

That is correct.

> For example if you loop thru 100,000 rows and for each iteration, call
> a function that does an update and commit, your 'granularity' of
> committing is far too small - performance will suffer.

Absolutely.

> Is that not reasonable, or am I misapprehending how Oracle and called
> subprograms and calling processes work?
>
> Thanks.
>
> Ken

Very reasonable ... just not what I understood from your first posting.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri Oct 29 2004 - 20:31:33 CDT