Re: DDL

"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 Received on Thu Nov 18 2004 - 13:44:29 CST