File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Rolling back Oracle DDL" Watch "Rolling back Oracle DDL" New topic

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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

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

Joined: May 26, 2003
Posts: 33102

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

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

Ask Tom about Temporary Tables
I agree. Here's the link:
subject: Rolling back Oracle DDL
It's not a secret anymore!