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;
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.