• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Rolling back Oracle DDL

 
Gobind Singh
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4007
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can always write a drop table command to rollback the create.
 
Lucas Lee
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The DDL command is not support transaction,therefore you can't rollback the DDL command.
 
Chris Hendy
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oracle does an implicit commit before and after each DDL statement, so no you can't rollback.
 
Gobind Singh
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This might be of interest to you

Ask Tom about Temporary Tables
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic