Re: Why Oracle does not allow rollback of DDL statements?
Date: Tue, 11 Nov 2008 06:34:27 -0500
Message-ID: <6nt8u4Fpec5U1@mid.individual.net>
Noons wrote:
> On Nov 10, 11:06 pm, Serge Rielau <srie..._at_ca.ibm.com> wrote:
>
>> That's the first I hear about that. Can you point to any docs explaining >> the behavior you imply? I'm also told that SQL Server (like DB2) has >> transactional DDL. Even TRUNCATE table is transactional.
>
> Instead of reading marketing materials, try using the product.
Unfortunately my job forbids me to actually use other DBMS's (lawyers...). So I'm stuck with asking questions. :-)
> It is supposed to *be* there. However, it is so inconsistent as to
> make it essentially unusable.
> 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? 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.
> Now, try to select form an incorrect table name - another common
> example
> os a "oops" statement in an u[grade script. It produces an error, and
> DOES
> put the transaction in a suspended state that is neither rollback nor
> commit:
> if you are running the script interactively, you then have to manually
> take a
> decision on which way it goes.
Interesting. In my world again the statement would fail (and roll back)
but the transaction remains intact. An exception handler initiates
rollback if desired.
Cheers
Serge
-- Serge Rielau DB2 Solutions Development IBM Toronto LabReceived on Tue Nov 11 2008 - 05:34:27 CST