Re: Oracle does not rollback correctly after ENABLE CONSTRAINT failed

On Feb 5, 11:46 am, "John K. Hinsdale" <h..._at_alma.com> wrote:
> > On 5 feb, 16:52, uha..._at_gmail.com wrote:
> > > After a failed attempt to enable foreign key the rollback did not
> > > return to the original database state.
>
> On Feb 5, 11:49 am, "Carlos" <miotromailcar..._at_netscape.net> wrote:
>
> > DDL always do an implicit COMMIT. It is done BEFORE the DDL. So, it
> > doesn't matter if the DDL succeeds or fails: the commit is done (no
> > chance for a rollback then).
>
> My observation is otherwise. This is probably a policy of the client
> software that establishes and maintains the connection to
> Oracle. For SQL*Plus, it does seem to keep the transaction
> alive (uncommitted) and available for rollback, at least for an
> intervening, failed CREATE TABLE statement. See transcript
> below for client and server V. 10.2.0.1.0. One well know behavior
> SQL*Plus is that it WILL commit in-progress changes on exit.
>

Aside from the actual answer (from Maxim, parse error) about the before and after commit for DDL, it's also long been documented:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c15trans.htm#297 http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/sqlplsql.htm#i18507

It's a pretty important concept to understand if you are trying to deal with transactions and DDL.

jg

--
@home.com is bogus.
If property is theft, intellectual property owners should be sent to
Siberia.  http://www.cnn.com/2007/TECH/02/05/gorbachev.gates.reut/index.html?eref=rss_topstories