I have script, that contains several DDL-statements. For example script removes FK-constraint from a column, then it renames the column, then it puts new data to the column, then it puts new FK-constraint to the column, also some other ALTER-statements are done, and with several tables.
I want to test my such script. I want to test so that if something in my long script throws error, then i can rollback all made ddl changes and fox the script and rerun the script again. How can i do this?
It could be something like this:
begin DDL tran;
when others then
rollback all ddl
end DDL tran;
You cannot ROLLBACK a DDL. Because Oracle issue COMMIT before and after execution of the DDL statement.
But the best way to write these DDL Script is to include the command to drop the newly created object in the script itself.
I would say like this.
Rollback DDL is allowed in SQL server but not in ORACLE. Here you have to drop the object and recreate it with desired definition.
Only if you DROP a table, you can falshback them in oracle versions 10g and after.