Re: procedure submitted to dbms_job - implicit commit?

From: Palooka <nobody_at_nowhere.com>
Date: Wed, 23 Jul 2008 23:38:03 +0100
Message-ID: <hpOhk.12716$9d4.7477@newsfe17.ams2>

joel garry wrote:

> On Jul 23, 1:07 pm, Palooka <nob..._at_nowhere.com> wrote:
>> No gazzag wrote:
>>> On 23 Jul, 08:33, steph <stepha..._at_yahoo.de> wrote:
>>>> On 22 Jul., 20:06, joel garry <joel-ga..._at_home.com> wrote:
>>>>> On Jul 22, 7:13 am, steph <stepha..._at_yahoo.de> wrote:

>>>>>> Hi group,
>>>>>> I'm just wondering - and I tried to look it up in the documentation:
>>>>>> When I submit a dbms_job calling a pl/sql-procedure and this procedure
>>>>>> does some data-changes - is it for sure that this changes are
>>>>>> commited? (There is no commit in the procedure.)
>>>>>> I've tried with a little test-case and the job's changes seem to be
>>>>>> commited - but I'm not sure if this is standard behaviour. Is it
>>>>>> possible to submit a job that does no implicit commit?

>>>>> Besides what Dan Blum noted, see metalink Note:61730.1.

>>>>>> Thanks,
>>>>>> stephan
>>>>>> Oh, my version of Oracle is 10g - but I assume my question is general
>>>>>> and not version-dependent ...

>>>>> Seehttp://www.orafaq.com/maillist/oracle-l/2005/02/09/0440.htm(I
>>>>> think there are more specific posts floating around somewhere that
>>>>> explain how things change, but that was found with a quick google).
>>>>> Also seehttp://www.pythian.com/blogs/398/dbms_scheduler-and-implicit-commits
>>>>> jg
>>>>> --
>>>>> @home.com is bogus.
>>>>> "A pilot who doesn't have any fear probably isn't flying his plane
>>>>> to its maximum." - Jon McBride, astronaut
>>>> oh thanks, that explains. but surely it's better to issue explicit
>>>> commits and not to rely on the implicit one - and if it's only to make
>>>> the code more readable
>>>> regards,
>>>> stephan- Hide quoted text -
>>>> - Show quoted text -
>>> As Dan quoted from Oracle's own documentation: ""You should explicitly
>>> commit or roll back every transaction."
>>> So, commit (or rollback, depending on your logic) as required.
>>> HTH
>>> -g
>> No disagreement with anything that has been said, but since OP is on
>> 10g, would it not be better practice to be using DBMS_SCHEDULER?
>>
>> Palooka
> 
> Yeah, I was hoping the pythian blog reference would key people to
> that.  But far be-it for me to dictate that advice without knowing the
> scope of the OP's issue.
> 
> jg
> --

Oops. Missed the pythian blog reference; sorry. But your point about the scope of OP's issue is well made.

Cheers.
Palooka Received on Wed Jul 23 2008 - 17:38:03 CDT