Re: trying to understand transaction control in pl/sql

Hi John, thanks for your reply.

I think you're right about the impicit savepoints - actually it seems that it defines different levels of savepoint, as pointed out by Xho.

John - if you check out case2 below, if an error occurs in a subprogram and is not trapped at ANY level, Oracle backs up all the way to the start of the highest-level savepoint - in case2, the unnamed block that calls the subroutine.

My 'take' - which is really just restating what everybody has been saying:

At the level of the sqlplus prompt, every update/insert/delete has one implicit savepoint, and also the invocation of any unnamed block.

Below that, the unnamed block itself has 'sub' savepoints - one for each insert/update/delete statement in it, and one for each subprogram unit.

And so on down the line.

If an error occurs, and that error is handled at ANY level by the time we're back at the sqlplus prompt, we only rollback to the immediate savepoint at the start of the update/insert/delete that errors. Otherwise we rollback to the top-level 'virtual' savepoint currently in existence, which is my offending unnamed block. That is, a handled error is handled and so can be dealt with without rolling back all the way to the top. It is handled and the transaction proceeds.

Is it true that only commits define the end of a transaction (and start of a new one) - rollbacks only define the end of a transaction if they rollback to the last commit, rather than savepoint (whether explicit or implicit)?

I realize I'm just restating what you all have been saying, and probably with at least a few errors and misconceptions Thanks for your feedback though.

I came to my 'version' from the following by no means exhaustive tests:

case1

--
I created a table a with
one column, a1 number, and at the sqlplus prompt inserted a row with
a1 = 1. I then ran that unnamed block I referred in an earlier post that, WITHOUT an
exception handler, does the following:

insert into a values (2);
insert into a values (3);
insert into a values ('a');

As expected I get an unhandled error on the last line. When I do a select
for everything in the table a, I get the first row I inserted 'manually',
the one with a1 = 1.

So there seems to have been an invisible savepoint set just before the
unnamed block ran.

case2
--

Then I modified the unnamed block so it did two good inserts and then 
called a stored procedure that did two good inserts and ended with one 
'bad' - inserting a character into a number column. The stored 
procedure also had no error trap.

When I run this one, as expected, error message. When I select everything 
from the table, it gets that single row with a1 = 1.

Again, the unnamed block seems to set an invisible savepoint. And 
everything in the stored procedure got rolled back.

case3
--

Then I reran everything, except the unnamed block had a generic when 
others then null; error trap, and the stored procedure had a generic 
when others the null; error trap.

In this case as expected, no error message was generated, and when I 
selected * from the table, it had inserted all the rows that were valid 
and only failed to insert the 'bad' rows.

case4
--

Then I deleted everything from the table a except the a1 = 1 and did 
a commit.

Then I reran everything just as in case3, except that: the stored 
procedure had NO error trap but the unnamed block that calls it DOES. 
The result was exactly the same as in case3 - everything was stored 
except 'bad' rows.

case5

-----

Then I deleted everything from the table 1 except the a1 = 1 and did 
a commit.

Then I reran everything just as in case4, except that the stored 
procedure was the one with the error trap and unnamed block the one 
without an error trap.

The results were that everything was stored in the table except the 
'bad' lines.

case6
-----

Finally ran case where my unnamed block did some ok inserts, I called 
a proc that did some more ok updates, then I called a proc that did 
some ok inserts and a bad insert; and there were no error traps in any 
proc or block. Everything got rolled back.

John Russell <netnews10_at_johnrussell.mailshell.com> wrote in message news:<gbl3o0ph63dpval0uupf7rb8nse953libb_at_4ax.com>...


> On 25 Oct 2004 05:44:05 -0700, kquirici_at_yahoo.com (ken quirici) wrote:

> 

> >Hi,

> >

> >I'm observing behavior in 9i about transactions in pl/sql unnamed blocks that

> >seems undocumented (altho I find the existing doc. hard to understand anyway),

> >and I wonder if anyone can explain it in terms of some notion of

> >transactional 'boundaries' - when a transaction starts, when it ends, within

> >a pl/sql unname block. It's probably obvious but I have brain lock at the moment.

> >

> From:

> 

> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/06_ora.htm

> 

> How Oracle Does Implicit Rollbacks

> 

> Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks

> an implicit savepoint (unavailable to you). If the statement fails,

> Oracle rolls back to the savepoint. Normally, just the failed SQL

> statement is rolled back, not the whole transaction. However, if the

> statement raises an unhandled exception, the host environment

> determines what is rolled back.

> 

> If you exit a stored subprogram with an unhandled exception, PL/SQL

> does not assign values to OUT parameters. Also, PL/SQL does not roll

> back database work done by the subprogram.

> 

> John