Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: DDL
Hi Howard
This is all getting a bit silly but anyway's, a few final comments embedded.
"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:419e6f7c$0$25116$afc38c87_at_news.optusnet.com.au...
> Richard Foote wrote:
>> "David Fitzjarrell" <fitzjarrell_at_cox.net> wrote in message >> news:9711ade0.0411181508.16af523e_at_posting.google.com... >> >>>DA Morgan <damorgan_at_x.washington.edu> wrote in message >>>news:<1100761866.606335_at_yasure>... >>> >>>>Rahul Jain wrote: >>>> >>>> >>>>>Why is DDL required to be the last statement in a transaction? >>>> >>>>It isn't. Where did you hear this? >>> >>>Presuming you mix DML and DDL in the same script or set of commands >>>(which, with a few limited exceptions like dropping or creating a temp >>>table, I question), to preserve the completeness (for lack of a better >>>word at the moment) of the transaction the DDL should be placed AFTER >>>any inserts, updates and/or deletes to data. The OP has worded this >>>incorrectly, and English, for non-English speakers (and some who claim >>>it as their primary tongue) is a cruel taskmistress. To be correct: >>> >>>If it is necessary to mix both DML and DDL the DDL should follow any >>>statements comprising a single transaction, as the DDL will issue a >>>commit prior to its execution, and another commit afterwards. Note >>>that the DDL will commit the prior transaction BEFORE it executes, so >>>it isn't truly a part of the transaction itself; the DDL starts a new >>>transaction comprised solely of the DDL. >>> >>>So, Howard is correct, and Daniel and Richard are also correct, since >>>the original question can be read in a number of ways. Howard read it >>>as I did initially, asking, "Why must I put my DDL at the end of a >>>transaction?" Daniel and Richard read it as it was written, >
This reminds very much of those pictures that you had to both squint and defocus in order to see. Some people managed to see them really easily, some struggled and just didn't get it.
Howard, please squint a little harder ...
>
My point in this whole sorry saga is that a DDL is generally *not* the last statement in a transaction and that your question above is indeed poorly written. Example:
update emp
set sal = 1000
where id =1234;
commit;
The above is without question a transaction.
The last statement is a COMMIT, not some DDL.
Therefore in answer to *your* question above, "Why *is* DDL the last statement in a transaction", the correct answer is that it's not, it's the *commit* (or rollback ) that is more commonly the last statement in a transaction.
That's the fuzzy wuzzy picture you haven't been seeing. That's all it is !!
>
Now re-read the OP question , now re-read my initial response, now re-read David's post and see how I and others initially interpreted the question.
>
>
I guess the question must be a tad ambiguous considering the epic this thread's become !!
> >>>and their >>>responses are also correct, in that there is no requirement to include >>>DDL at the end of a transaction. >
No, the assumption is that DDL is required to end a transaction when all it takes is a commit. There is no need for *any* DDL to be in a transaction at all. *None*.
>
> Trouble is, that's not right.
Trouble is we're both right. It's just that you've always assumed there to be some DDL involved somewhere along the line. I haven't.
I guess we've both squinted and seen different pictures...
>
The OP question again "Why is DDL required to be the last statement in a transaction?". My example again:
update emp
set sal = 1000
where id =1234;
commit;
Was DDL *required* to be the last statement in the above transaction ? No.
The answer I gave is no. Look above, no DDL. Get it ?
>
Close but perhaps not close enough for dumbos like myself !!
> Now, since this involves digging out the dictionaries, I expect this to be
> the end of the matter, as it veers away from technical Oracle matters and
> descends to the depths of textual interpretation. I just think people (and
> I'm not directing this at you alone or indeed primarily, David) should be
> a little more careful about claiming that what other people have written
> is "poor English" or "incorrect grammar", or to reach for what seems to be
> fast becoming the 'funny little colonials' defence.
>
I thought it was the end of the matter several posts ago. I haven't disagreed with what you've been saying, I've seen your "picture" all the time. Hopefully, finally, you'll see mine.
Please, let this be the end ...
Cheers
Richard Received on Fri Nov 19 2004 - 18:47:16 CST