How can I execute multiple DDL statements within a transaction in Oracle?

Question

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

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.