Re: Oracle transactions and DDL statements.

<peter.koch.larsen_at_gmail.com> wrote in message news:1147252750.112378.67450_at_i40g2000cwc.googlegroups.com...
>
> Jim Kennedy skrev:
>
> > <peter.koch.larsen_at_gmail.com> wrote in message
> > > I do not have uncommitted data per se, but when I change the
datamodel,
> > > this change involves several tables and triggers and might span more
> > > than one database (only one of them being from Oracle), There are also
> > > non-database subsystems that participate in the distributed
> > > transaction. The problem occurs if e.g. the non-database transaction
> > > must abort the transaction for one reason or the other.
> > > >
>
> [snip]
>
> > You shouldn't be creating objects in the middle of a transaction. Start
or
> > end the transaction before creating objects.
> > Jim
>
> But what if my transaction requires the creation or destruction of ten
> tables? This is what I wrote above, this is what the software does and
> this what caused it to fail: you can't abort it after having created
> the first five tables as those table alerady have been created.
>
> /Peter
>

That's my point it is a very bad practice. The "requirement" is not a requirement it is a solution. Business cases usually don't say create temporary tables ... They usually say "The ATM withdraw transaction needs to check the balance of the account and if the balance is >= the withdraw amount then ..."

Creating objects on the fly is a very unscalable practice and it is rare that one ever needs to do so. (other RDBMS's sometimes use them as a strategy due to their locking models eg MS SQLServer, Oracle doesn't need them for that purpose) Oracle does have global temporary tables. You create the global temporary tables in advance and indicate if you want the rows in the table to live in a transaction or in a session. (see docs for syntax)

I would look at using global temporary tables. Your solution will scale much better, and you won't have this transaction problem. It would actually simplify your code a lot. (just get rid of the object creation and object drops)

Jim Received on Wed May 10 2006 - 08:43:37 CDT