Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

procedure submitted to dbms_job - implicit commit?

798 views
Skip to first unread message

steph

unread,
Jul 22, 2008, 10:13:46 AM7/22/08
to
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?

Thanks,
stephan


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

Dan Blum

unread,
Jul 22, 2008, 10:26:22 AM7/22/08
to
steph <step...@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?

From Chapter 6 of the PL/SQL User's Guide and Reference:

"You should explicitly commit or roll back every transaction.
Whether you issue the commit or rollback in your PL/SQL program or from
a client program depends on the application logic. If you do not commit
or roll back a transaction explicitly, the client environment determines
its final state."

Clearly DBMS_JOB commits automatically (although I imagine it issues a rollback
if the job throws an error). This is really the required behavior - if it didn't,
how would you commit the changes? Bear in mind that the job will run in its own
session.

--
_______________________________________________________________________
Dan Blum to...@panix.com
"I wouldn't have believed it myself if I hadn't just made it up."

joel garry

unread,
Jul 22, 2008, 2:06:15 PM7/22/08
to
On Jul 22, 7:13 am, steph <stepha...@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 ...

See http://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 see http://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

steph

unread,
Jul 23, 2008, 3:33:59 AM7/23/08
to
On 22 Jul., 20:06, joel garry <joel-ga...@home.com> wrote:
> On Jul 22, 7:13 am, steph <stepha...@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

gazzag

unread,
Jul 23, 2008, 7:25:51 AM7/23/08
to
> 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

Palooka

unread,
Jul 23, 2008, 4:07:38 PM7/23/08
to
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

joel garry

unread,
Jul 23, 2008, 5:17:02 PM7/23/08
to

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
--
@home.com is bogus.

Spider early and often. http://www.pcpro.co.uk/news/214371/google-blogger-hosts-2-of-worlds-malware.html

Palooka

unread,
Jul 23, 2008, 6:38:03 PM7/23/08
to
Oops. Missed the pythian blog reference; sorry. But your point about the
scope of OP's issue is well made.

Cheers.
Palooka

0 new messages