Re: [Info-ingres] Friday is coming ...

At 10:49 AM -0400 7/20/05, Karl & Betty Schendel wrote:
At 9:29 AM -0500 7/20/2005, Michael Leo wrote: 
Gang,

We usually save the contentious arguments for Fridays ... but
maybe we should wait for Roy to come back from vacation before
discussing the question a client of mine recently asked:

  "Why would you want DDL statements to be transactional?"


Uh, gee ... maybe because the DDL is part of my transaction, i.e. my
unit of work, which I want to commit or rollback atomically?  Could
that be it?

I'd invite the client to explain why DDL should be any different from DML.

--
Karl R. Schendel, Jr.         schendel@xxxxxxxxxxxxxx
Computer Associates Ingres Development


Two things:

  1. I don't disagree with you.  I'm just trying to make trouble.

  2. As you probably know, Oracle DDL statements, while atomic,
     are not transaction friendly.  Executing a DDL statement issues
     an implicit commit within the Oracle engine.  I was pointing out
     the the customer how evil this is, but they couldn't understand
     why.   *Sigh*

     Just to be clear, in Oracle, this SQL sequence results in a
     row actually existing in the wow table and the wow2 table
     exists as well.

      create table wow(neato varchar2(20));
      commit;
      insert into wow values ('Mike');
      create table wow2(neato varchar2(20));
      rollback;
      select * from wow;
      commit;

Now that's just wrong.

Cheers,

--
Mikey
.

Relevant Pages

  • Re: Using ADODB(MS ActiveX Data Objects) with C#, transaction problem.
    ... AFAIK, in Oracle anyway, "DDL statements" will always commit a transaction ... But this is likely Oracle version and provider dependent. ...
    (microsoft.public.data.ado)
  • Re: Unifying Temp table behavior across oracle, mssql
    ... CREATE GLOBAL TEMPORARY TABLE foo ON COMMIT PRESERVE ROWS; ... Dropping the table is simple - only on oracle does the truncation seem ... Because we have to support SQL Server, we have to be careful about ...
    (comp.databases.oracle.server)
  • RE: Slow connection to Oracle 9i
    ... A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. ... Slow connection to Oracle 9i ... do not get a transaction too long error (can't remember exactly what its ...
    (perl.dbi.users)
  • Re: up front designs always useless
    ... >> so if I don't know up front whether I am going to use XML or an Oracle ... You can commit to risky decisions with little impact of changing ... decisions until as late as possible is an up front design decision. ...
    (comp.object)
  • Re: Why Oracle does not allow rollback of DDL statements?
    ... DDL statements are executed. ... I was learning SQL Server few months ago and I noticed that SQL Server ... like there is something in Oracle architecture which prevents rollback ... in Oracle you don't execute DDL statements on the fly. ...
    (comp.databases.oracle.server)