Thread: AW: [HACKERS] Re: [GENERAL] drop/rename table and transactions

Vadim wrote:
> > The developers appear to want to make them 
> > work (i.e., have the
> > ability to rollback a DROP TABLE, ALTER TABLE ADD COLUMN, 
> > etc.). This, in my
> > opinion, goes far above and beyond the call of duty for a 
> >  RDBMS. Oracle issues
> > an implicit COMMIT whenever a DDL statement is found. 
>
> And I agreed with this.

And I strongly disagree. 
This sounds like pushing the flush button in the toilet,
and instead of the toilet flushing you get a shower.

How could anybody come to the idea that a DDL statement 
also does a commit work if inside a transaction ?

Now this sound so absurd, that I even doubt Oracle would do this.

Andreas

Zeugswetter Andreas SEV wrote:
> 
> > >  RDBMS. Oracle issues
> > > an implicit COMMIT whenever a DDL statement is found.
> >
> > And I agreed with this.
> 
> And I strongly disagree.
> This sounds like pushing the flush button in the toilet,
> and instead of the toilet flushing you get a shower.
> 
> How could anybody come to the idea that a DDL statement
> also does a commit work if inside a transaction ?
> 
> Now this sound so absurd, that I even doubt Oracle would do this.

Standard says (4.41  SQL-transactions):
        It is implementation-defined whether or not the non-dynamic or        dynamic execution of an SQL-data
statementor the execution of                                ^^^^^^^^^^^^^^^^^^        an <SQL dynamic data statement>
ispermitted to occur within the                                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^        same
SQL-transactionas the non-dynamic or dynamic execution of        ^^^^^^^^^^^^^^^^^^^^^^^        an SQL-schema
statement.If it does occur, then the effect on any        ^^^^^^^^^^^^^^^^^^^^^^^
 

So, you see that this idea came not to Oracle only...

I don't object against DDLs inside BEGIN/END.
I just mean that it's not required by standard.
If someone is ready to fix this area - welcome.

Vadim
P.S. Is DROP TABLE rollback-able in Informix, Andreas?

Zeugswetter Andreas SEV wrote:

> Vadim wrote:
> > > The developers appear to want to make them
> > > work (i.e., have the
> > > ability to rollback a DROP TABLE, ALTER TABLE ADD COLUMN,
> > > etc.). This, in my
> > > opinion, goes far above and beyond the call of duty for a
> > >  RDBMS. Oracle issues
> > > an implicit COMMIT whenever a DDL statement is found.
> >
> > And I agreed with this.
>
> And I strongly disagree.
> This sounds like pushing the flush button in the toilet,
> and instead of the toilet flushing you get a shower.
>
> How could anybody come to the idea that a DDL statement
> also does a commit work if inside a transaction ?
>
> Now this sound so absurd, that I even doubt Oracle would do this.
>
> Andreas

I hate to disappoint your faith in Oracle,  but....
(from the Oracle 7 SQL Lanugage Reference Manual):

--------------
Transactions

A transaction (or a logical unit of work) is a sequence of SQL
statements that ORACLE treats as a single unit. A transaction
begins with the first executable SQL statement after a COMMIT,
ROLLBACK or connection to the database. A transaction ends with
a COMMIT, ROLLBACK or disconnection (intentional or unintentional)
from the database. Note that ORACLE issues an implicit COMMIT
before and after any Data Definition Language statement.

You can also use a COMMIT or ROLLBACK statement to terminate
a read only transaction begun by a SET TRANSACTION statement.
--------------

Since ORACLE has 70% of the RDBMS market, it is the de facto
standard that the RDBMS will issue an implicit COMMIT when
processing a DDL statement. Like I said before, I would LOVE to
have working support for ROLLBACKs of DDL statements. But I
would prefer to have implicit COMMITs over corrupted indexes,
tables, and mandatory DBA intervention.

Mike Mascari