Re: trying to understand transaction control in pl/sql

Hi Manfred,

Thanks for your reply.

I understand what you've written. My question is really, what is the definition of a transaction. I checked either the version 9 or 10 Oracle documentation, and it said that a transaction starts with as you indicate an insert, or the like; and, to conform to some standard or other, continues until it hits a commit or a rollback, at which time the entire transaction is committed or rolled back.

I seem to be stuck on my program examples, which on an error show either a rollback of just the last insert of 3, the one that caused the error, or all three, depending on whether or not there's an error handler. My question is, why is there this inconsistency? Why does simply having or not having an error handler change the scope of the rollback from the statement which caused the error to all the inserts in the block?

I have an awful feeling the answer is obvious, but I just can't clear the cobwebs away.

Thanks all for your help. Maybe I'm hopeless.

Ken

Manfred Peter <pnirff_at_t-online.de> wrote in message

news:<clm80d$2iv$00$1_at_news.t-online.com>...
> 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
>
Received on Wed Oct 27 2004 - 10:10:05 CDT