Re: trying to understand transaction control in pl/sql

From: John Russell <netnews10_at_johnrussell.mailshell.com>
Date: Fri, 29 Oct 2004 05:27:53 GMT
Message-ID: <gbl3o0ph63dpval0uupf7rb8nse953libb@4ax.com>

On 25 Oct 2004 05:44:05 -0700, kquirici_at_yahoo.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.
>

From:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/06_ora.htm

How Oracle Does Implicit Rollbacks

Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.

If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters. Also, PL/SQL does not roll back database work done by the subprogram.

John Received on Fri Oct 29 2004 - 00:27:53 CDT