Re: How to call scripts from another scripts ?

In message <1173943177.561304.324260_at_y80g2000hsf.googlegroups.com>, krislioe_at_gmail.com writes
>Hi all,
>
>During development, we make database structure/constraint
>modifications into sql file (scripts). So I will have some single
>script files, e.g : script1.sql, script2.sql, script3.sql etc.
>
>Then I create a mainscript.sql to call all the scripts. So I need only
>to execute @mainscript.sql, which in turn execute script1.sql,
>script2.sql, script3.sql.
>
>mainscript.sql :
>@script1.sql;
>@script2.sql;
>@script3.sql;
>commit;
>
>The question is :
>How can I rollback if one of the script fails ?
>
>Thank you for your help,
>xtanto
>

Tricky. You seem to be talking about DDL and DDL cannot be rolled back.

In development the best approach is probably to have a baseline dump file (or a baseline script if there is no data involved). At each iteration, drop all objects, load the baseline and then the modification scripts. If a mod fails, reload the baseline.

For live deployment you would need to backup the database and recover to a point in time if the upgrade fails. This is easier with 10g where you can use flashback database.

How feasible this is will depend on your service level requirements etc.

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>