Re: trying to understand transaction control in pl/sql

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.

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.

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

Thanks.

Ken Received on Thu Oct 28 2004 - 11:11:38 CDT