oracle - Rerunning a script of scripts -

at work, use lot of pre-written scripts when doing production deployments, started creating master scripts containing these scripts use, save typing. add 'pause' commands between each script if there's error, can break out of master script , fix problem. @ point, have edit master script , delete has run doesn't re-run before can start master script again. know there no goto command in sqlplus, there way of writing sql script can pick left off?

just clarity, master script looks this:

@script1.sql 

pause script 1 completed. hit enter continue

@script2.sql 

pause script 2 completed. hit enter continue

   .    .    . 

if can create tables/procedures, can approach. have 3 scripts run:

script1.sql:

create table table_script_1(id number); 

script2.sql

create ta_error_here_!!!!!!_ble table_script_2(id number); 

script3.sql

create table table_script_3(id number); 

we can create table in store scripts run, in order of execution, , procedure handle table:

create table script_to_run (num number, script varchar2(256), status varchar2(10));  create or replace procedure update_script (p_script varchar2, p_status varchar2) pragma autonomous_transaction; begin     update script_to_run     set status = p_status     script = p_script;     commit; end; / 

in way, use table script have run, , in order:

insert script_to_run values (1, 'd:\script1.sql', 'to_run'); insert script_to_run values (2, 'd:\script2.sql', 'to_run'); insert script_to_run values (3, 'd:\script3.sql', 'to_run'); commit; 

at point, our main script read table, run first script still not executed , call himself recursively, run next script:

main_script.sql:

column script new_val script whenever sqlerror exit select script script_to_run num = (   select min(num)                 script_to_run                 nvl(status, 'ko') != 'ok' ); start &script exec update_script('&script', 'ok'); prompt 'script &script ok' start d:\main_script.sql 

now run main script ( while script2.sql contains errors) , check result:

sql> select 1 table_script_1;  no rows selected  sql> select 1 table_script_2; select 1 table_script_2               * error @ line 1: ora-00942: table or view not exist   sql> select 1 table_script_3; select 1 table_script_3               * error @ line 1: ora-00942: table or view not exist sql> select * script_to_run;         num script                                             status ---------- -------------------------------------------------- ----------          1 d:\script1.sql                                     ok          2 d:\script2.sql                                     to_run          3 d:\script3.sql                                     to_run 

only script1 ran ok, script2 had error3, , script3 never ran.

after fixing script2.sql, run again main script, without modifying it; in second run, main script executes script2 , script3, not script1. final result:

sql> select 1 table_script_2;  no rows selected  sql> select 1 table_script_3;  no rows selected  sql> select * script_to_run;         num script                                             status ---------- -------------------------------------------------- ----------          1 d:\script1.sql                                     ok          2 d:\script2.sql                                     ok          3 d:\script3.sql                                     ok  sql>