Home » SQL & PL/SQL » SQL & PL/SQL » Help managing transactions
Help managing transactions [message #194946] Tue, 26 September 2006 05:07 Go to next message
Messages: 49
Registered: June 2006
Location: Romania
Hy...i have the following table:

create table A

and the i need to issue the following statements:

insert into a(c1,c2)values(1,'a');
insert into a(c1,c2)values(2,'b');
create table aux(c1 number(1));
insert into a(c1,c2)values(3,'c');

the order of the statements can not be changed. The problem is that Oracle commits a transaction after creating the table "aux" (third line).
Can anyone give me a hint in how to manage the transaction to avoid commiting the first two lines??? Again, the order of the four statements conn not be chaged!!!
Re: Help managing transactions [message #194949 is a reply to message #194946] Tue, 26 September 2006 05:15 Go to previous messageGo to next message
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
There is no way to do what you are asking. You cannot avoid a commit on DDL.
Re: Help managing transactions [message #194950 is a reply to message #194946] Tue, 26 September 2006 05:16 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sorry, you're out of luck.
Any DDL statement will commit any outstanding changes.
So if you have to do two inserts, followd by a CREATE TABLE then you'll always have two committed inserts.

DDL really shouldn't be used as part of the processing - there are very very few cases where you actually need to create and drop table in the middle of processing, particularly now that GLOBAL TEMPORARY TABLES exist.

Previous Topic: Join Style Performance
Next Topic: 01013: user requested cancel of current operation
Goto Forum:

Current Time: Wed Aug 23 14:15:01 CDT 2017

Total time taken to generate the page: 0.12923 seconds