Re: trying to understand transaction control in pl/sql

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 26 Oct 2004 09:18:10 -0500
Message-ID: <uoeipftw8.fsf@standardandpoors.com>

On 26 Oct 2004, kquirici_at_yahoo.com wrote:
> Kenneth Koenraadt wrote in message
> news:<417d26ae.832968_at_news.inet.tele.dk>...

>> 
>> 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.
>> 

>
> Hi Kenneth,
>
> Does this mean a transaction in a pl/sql unnamed block (to
> start with) runs from the start of the block to the first
> commit or rollback or savepoint or set transaction?

If you have a commit, then at the time Oracle reaches it, it commits, but it still finishes the block.

> The second to the next, etc. (except for called autonomous
> transaction procs)? So each statement in the pl/sql unnamed
> block is not itself a transaction, even though they would be if
> individually typed in sequence at the sqlplus prompt?
>
> Hope I'm being clear.

Commit is not a PLSQL command telling Oracle to jump out of PLSQL blocks or anything remotely like that. It is a command that says to Oracle, commit the current transaction. That is it, nothing else. If you have told it to do other things after the commit, then, it will do those things.

Learning PLSQL doesn't mean you've learned transaction handling and vice-versa.

-- 
Galen Boyer

Received on Tue Oct 26 2004 - 09:18:10 CDT