Re: trying to understand transaction control in pl/sql

Hello Ken,

when you start a dml like insert, update or delete, the transaction will be opened (After connecting to a database). If you want to commit the current transaction, the type commit.

begin

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

   COMMIT;
end;

But this is not a good programing style as somebody could call your procedure or function and the caller can not decide wether to commit the transaction or to rollback it.

Better:

Start the procedure with your tool (SQLPLUS or whatever) an type COMMIT after the procedure returns.

Note:
All DDL Statements like CREATE TABLE, CREATE VIEW and so on - commits the current transaction automatically.

unnamed blocks has nothing to do with transactions.

See Oracle documentation for using savepoints.

Hope this helps ?

Best regards
Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.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
>
>
Received on Tue Oct 26 2004 - 14:17:25 CDT