Re: trying to understand transaction control in pl/sql

From: Manfred Peter <m.peter_at_alligatorsql.com>
Date: 31 Oct 2004 03:12:05 -0800
Message-ID: <a3199c1.0410310312.e91785@posting.google.com>

Dear Ken,

there is no inconsistency. When you start you example without an error handler,
the first two insert will be executed and the thrid causes an exception.
You do not catch the execption and therefore you will see the error in the
caller program like sql plus or whatever. The transaction, that your anonymous
block have stared is still active since you have end your transaction (with commit or rollback) - or you close the connection, which will cause a standard rollback (sometime you IDE will send a commit - when you close the application).
So, when the transaction is still open, no other connection can see the inserted records.
When you start you´re program with an exception handler, the third insert will also cause the exception, but you have catched this exception with your exception block.
PL/SQL jumps into the exception block, but there is only a NULL statement, so PL/SQL does nothing and returns to the caller. In this example, the first
row insert are executed and the third isn´t. There is no difference regarding the transaction handling. Try this:

.
.
.

EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('Error : ' || SQLERRM || ' Code: ' || SQLCODE);
END; And you will see the error in your output window of your IDE.

Hope this was helpful ?

Best regrads
Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com
kquirici_at_yahoo.com (ken quirici) wrote in message news:<eeca902a.0410270710.2c750fea_at_posting.google.com>...
> 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 Sun Oct 31 2004 - 05:12:05 CST