Re: DDL

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 19 Nov 2004 07:49:50 +1100
Message-ID: <419d0aef$0$27449$afc38c87@news.optusnet.com.au>

David Fitzjarrell wrote:

> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<419c730a$0$25115$afc38c87_at_news.optusnet.com.au>...
> 

>>DA Morgan wrote:
>>
>>>Howard J. Rogers wrote:
>>>
>>>
>>>>Rahul Jain wrote:
>>>>
>>>>
>>>>>Why is DDL required to be the last statement in a transaction?
>>>>
>>>>
>>>>
>>>>Because there is an implicit commit before and after each and every
>>>>DDL statement.
>>>>
>>>>Regards
>>>>HJR
>>>
>>>
>>>You are absolutely correct Howard ... but I fail to see what your answer
>>>has to do with the question. But then its late and I'm tired.
>>
>>
>>Well, I'm no developer, but I was always taught that a transaction
>>starts with some piece of DML and ends when you say 'commit' (or
>>rollback, of course).
>>
>>Insert money into new bank account;
>>Delete money from old account;
>>Commit both changes;
>>
>>If DDL implies a commit, surely that therefore must count as the
>>termination of a transaction? And therefore by definition, the DDL
>>statement becomes the last statement in a transaction.
>>
>>Insert money into new bank account;
>>Delete money from old account;
>>Drop table EMP;
>>
>>...which achieves exactly the same thing as before.
>>
>>Since both you and Richard appear to agree that a piece of DDL is not
>>necessarily the last statement in a transaction, I guess I must be
>>missing something. Enlightenment from either would therefore be appreciated.
>>
>>Rgrds
>>HJR

> 
> 
> Straight from the Oracle documentation at tahiti.oracle.com:
> 
> "DDL statements implicitly commit the preceding and start a new
> transaction."
> 
> (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c16sqlpl.htm#478)
> 
> Pretty much says it all, right there.  DDL statemetns WOULD BE the
> last statement for a transaction, should there be any DDL to execute,
> since the DDL begins an entirely new transaction on its behalf.  It
> wouldn't matter that the DDL statement was placed in the middle of a
> script chock full of DML, the transaction immediately preceding the
> DDL statement would be committed just prior to the DDL statement
> execution, and a second commit, for the new DDL transaction, would be
> executed.
> 
> I cannot see any ambituity here.
> 
> David Fitzjarrell

Does that mean I'm right?

:-)
HJR Received on Thu Nov 18 2004 - 14:49:50 CST