Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to execute multiple DDL statements under a single transaction with Oracle database (10g etc.?) What I'm trying to accomplish is some sort of "upgrade" functionality of the database schema. I have a working implementation of this in SQL Server and I'm planning to port it to Oracle.

p-code follows:

start transaction

 CREATE TABLE A .....
 CREATE TABLE B ......

 INSERT INTO A....
 INSERT INTO B...


 commit transaction

 or error rollback transaction
share|improve this question

migrated from programmers.stackexchange.com Feb 21 '12 at 14:22

This question came from our site for professional programmers interested in conceptual questions about software development.

    
Have a look at a tool like Liquibase instead if you want version control for your database. –  Raku Feb 21 '12 at 14:07

1 Answer 1

You can issue multiple DDL statements in a single transaction using the CREATE SCHEMA command though you are limited to just the CREATE TABLE, CREATE VIEW, and GRANT statements.

CREATE SCHEMA AUTHORIZATION <<Oracle username>>
  CREATE TABLE a( col1 number, col2 number ) -- Note no semicolon
  CREATE TABLE b( col3 number, col4 number );

If you are trying to upgrade an existing database, depending on the Oracle edition and configuration, I would tend to suspect that you'd be better served with something like Oracle Flashback Database. If you create a guaranteed restore point before you start the upgrade, for example, you can simply flashback the database to that restore point if the upgrade fails regardless of how many transactions you've committed during the upgrade.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.