trying to understand transaction control in pl/sql

From: ken quirici <kquirici_at_yahoo.com>
Date: 25 Oct 2004 05:44:05 -0700
Message-ID: <eeca902a.0410250444.5cf587d@posting.google.com>

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 Received on Mon Oct 25 2004 - 07:44:05 CDT