Condition based spool generation in Oracle SQL script -

i have scenario generate spool based on condition.

the spool should generate if user types y.

column col noprint new_value elcm_script select decode(lower('&gen'),'y','c:\elcm.sql','n', 'null') col dual;  spool c:\elcm_details.spl  @&elcm_script spool off 

in above generate spool file if user type n error reading file.. how stop generating spool if n.or how remove spool generated/

if can put controlling section own script, e.g. elcm_ctl.sql, make that:

accept run_it char format a1 prompt "run script?"  set termout off  whenever sqlerror exit success rollback exec if upper(nvl('&run_it', 'n')) != 'y' raise no_data_needed; end if; whenever sqlerror continue  spool c:\elcm.sql select 'select sysdate dual;' dual; spool off  set termout on  spool c:\elcm_details.spl @c:\elcm.sql spool off 

the accept command bit neater relying on substitution prompts, in opinion, , self-documenting. prompts user specified string, rather 'gen'. (in sql*plus can extend , force user put in single character, re-prompting if put in longer; , default 'n' if press return without entering anything. sql developer supports subset of functionality).

then small anonymous block throws exception - doesn't matter 1 - if entered variable value not 'y' or 'y'. while it's doing i've set termout off don't see actual exception. , i've used whenever sqlerror make script exit when exception raised, whatever comes later not run. else in control script, not next query, have several sub-scripts if needed more flexible.

but in sql developer, termout works expected when run via @. if run contents of elcm_ctl.sql directly sql worksheet you'll see exception being raised, bit ugly. instead save control script , in empty worksheet do:

@c:\elcm_ctl.sql 

execute worksheet script , prompt you; if enter 'y' you'll see output of script in script output window (unless leave set termout off in control script), , create spool file. if enter else won't run elcm.sql file, won't show in script output window, , won't create spool file.