jQuery in Action, 3rd edition
The moose likes JDBC and Relational Databases and the fly likes Rolling back Oracle DDL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Building Microservices this week in the Design forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Rolling back Oracle DDL" Watch "Rolling back Oracle DDL" New topic
Author

Rolling back Oracle DDL

Gobind Singh
Ranch Hand

Joined: Aug 04, 2006
Posts: 62
Please can someone tell me if there is a way to rollback oracle 9i DDL statements using JDBC.

I am creating a table and I wish to roll back the creation if any exception occurs. I have found that oracle is not letting you do this.

any ideas?
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3740
    
  10

Off hand I don't think you can. I recall working with oracle recently and noticing table creation was immediate.

One question that stands out is why would you ever want to randomly create tables? Most applications run with fixed table structures with the exception of upgraders/installers. I can only hope the table creation isn't happening all the time or you can easily end up with unmanageable table sets. Please describe why you are creating tables on the fly and we might be able to help you try another technique.


[OCA 8 Book] [Blog]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 32508
    
214

You can always write a drop table command to rollback the create.


[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Lucas Lee
Ranch Hand

Joined: Oct 02, 2006
Posts: 53
The DDL command is not support transaction,therefore you can't rollback the DDL command.
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
Oracle does an implicit commit before and after each DDL statement, so no you can't rollback.
Gobind Singh
Ranch Hand

Joined: Aug 04, 2006
Posts: 62
Ok thanks to all for replying. I now understand that it is a limitation of oracle. (The reason I was trying to roll back DDL is because I am working with a Java program which runs reports. In order for this program to run a report, it creates lots of temporary tables on the fly in order to calculate metrics.) I wanted to roll these back.
Pete Hawdon
Greenhorn

Joined: Jul 19, 2006
Posts: 2
This might be of interest to you

Ask Tom about Temporary Tables
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Rolling back Oracle DDL
 
It's not a secret anymore!