Re: Why Oracle does not allow rollback of DDL statements?

From: Noons <>
Date: Thu, 13 Nov 2008 20:00:08 +1100
Message-ID: <gfgq8g$9nv$>

Serge Rielau wrote,on my timestamp of 11/11/2008 10:34 PM:

>> For example: Try to drop a table that is not there - a common example
>> of a
>> "oops" statement in an upgrade script.  It produces an error, but does
>> NOT
>> cause a rollback to take place or lock other scripts out.

> Why would is cause a rollback? If you are doing DML (let's move to safer
> ground) and you are getting an error - say a check constraint violation
> - do you expect teh whole transaction to roll back?

That is PRECISELY what was suggested as an advantage of the transactional DDL.

In SS's case, I'll make it clear:

if you MISTYPE the name of a table on an upgrade script - the example of applicability of said feature - you will end up with an incorrect script executed to the end, without so much as a rollback.

Transactional DDL is not something that is triggered or not depending on what is "convenient" to implement.

Either it works CONSISTENTLY and UNIFORMLY for ALL DDL statements or it is simply a joke, to be put right up there with the others - such as "web 2.0" and SOA.

 > Typically I'd expect
> the statement to fail (statement atomicity) and then an exception
> handler can decide whether the problem as serious enough to fail the
> transaction or recover from it somehow.
> That's what those WHENEVER exceptions are there fore.

And that's precisely what is missing from the SS implementation of transactional DDL.
Or at least a consistent way of implementing ALL exceptions the same way, be it by default or by code.

Otherwise, don't even mention upgrade scripts as an example of need for transactional DDL!

> Interesting. In my world again the statement would fail (and roll back)
> but the transaction remains intact. An exception handler initiates
> rollback if desired.

Exactly. I'd expect that to be the case, whatever the conditions. With SS, it's only if you code explicitly to cause that to happen: the default condition will bypass all that in inconsistent ways.

That makes the feature unusable for that purpose. Received on Thu Nov 13 2008 - 03:00:08 CST

Original text of this message