Re: pl/sql - auto commit

From: Ed Prochak <prochak_at_my-deja.com>
Date: Wed, 25 Aug 1999 16:16:13 GMT
Message-ID: <7q14s4$l2$1@nnrp1.deja.com>

In article <7pvbs9$p22$1_at_nnrp1.deja.com>, Nandakumar <N.Kumar_at_rocketmail.com> wrote:
> Do pl/sql sub programs 'COMMIT' for any INSERT/UPDATE operations
handled
> inside, on successful exit?
>
> --
> Nandakumar
> (N.Kumar_at_rocketmail.com)

No, not automatically. If you did not program a commit in the routine, then it won't do it. And I suspect you do not want to put the commit there in many cases. Why?

Becasue the COMMIT is for the session. Suppose you did some inserts that you haven't committed yet and you call this procedure. It does its work and does a commit. You then decide that you did not want those inserts, so you issuse a ROLLBACK command. Sorry but you are too late. The COMMIT in the procedure also committed your inserts.

Now it might be that you do want that commit to happen in the pl/sql routine. That's okay, just make sure the users of that routine understand that.

There is one other place where a commit might be done without being explicit. That's when you exit from SQLPLUS. (Hint: see the settings)

I hope this helps.

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3700 <<<NOTE new number

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Aug 25 1999 - 11:16:13 CDT