| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: trying to understand transaction control in pl/sql
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.
>
>I have a test table a(a1 number) initially empty.
>
>I run:
>
>begin
> insert into a values(1);
> insert into a values(2);
> insert into a values('a');
>end;
>/
>
>As expected an error results trying to insert the nonnumeric 'a' into a.
>When I select from a, it's empty.
>
>Then I run:
>
>begin
> insert into a values(1);
> insert into a values(2);
> insert into a values('a');
>exception
>when others then
> null;
>end;
>/
>
>Now of course I see no error since it's trapped. But when I look at the
>table a, it has two rows, with a1 = 1 and with a1=2.
>
>In the first case it seems the whole block is a transaction; but just
>adding the error trap (which doesn't do anything) makes each statement in
>the block a transaction (it seems).
>
>What's going on?
>
>Thanks.
>
>Ken Quirici
Hi Ken,
An *UNHANDLED* exception causes the on-going transaction to be rolled back. In the second example you handle the exception, thus no rollback.
If you do this :
begin
insert into a values(1);
insert into a values(2);
insert into a values('a');
raise;
end;
/
You reraise the exception, which is not handled => rollback, and again your table ends with no rows in it.
If you do this :
begin
insert into a values(1);
insert into a values(2);
commit;
insert into a values('a');
exception
when others then
raise;
end;
/
You end up with 2 rows in table a.
The 2 first inserts form one transaction, separated from the third insert with 'commit'. The third insert raises an unhandled exception, but the 2 first inserts are preserved in their own transaction which was committed before the exception was raised.
 |
 |