Re: Asynchronous Commit in Oracle Database 10g R2

"VC" <boston103_at_hotmail.com> wrote in message news:qKqdnffHUv-zOoTeRVn-og_at_comcast.com...
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:dfc0qd$ap2$1_at_nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>>
>>
>> The critical error is due to a design that does
>> not have 2PC, but we think we have introduced
>> an acceptable risk, when the optimisation has
>> actually hidden an unacceptable risk.
>
> If one is so smart as to use external procs from a P:/SQL block, one
> should be clever enough not to use commits in a loop (or even commits in a
> PL/SQL block altogether). On the other hand one can be too smart for
> one's own good ;)
>

True,

But you can have the same problem using utl_file or dbms_pipe - possibly even utl_smtp (although I haven't checked that). Any mechanism that allows you to get a message out of the black box before the call completes makes the optimisation a risk.

> Still, taking into account the fact that the effects of the optimisation
> are not documented, the optimisation is potentially dangerous as your
> example shows.
>

-- 
Regards

Jonathan Lewis

Currently changing publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 5th Sept 2005