0

I have a scenario where we generate spool based on condition.

The spool should generate only if user types y.

column col noprint new_value elcm_script
select decode(lower('&gen'),'y','C:\ELCM.SQL','n', 'null') col from dual;

SPOOL c:\ELCM_DETAILS.SPL 
@&elcm_script
spool off

In the above it will generate spool file even if user type n like error reading file.. How to stop generating the spool if n.or how to remove spool which is generated/

user3737084
  • 7
  • 8

1 Answers1

0

If you can put this controlling section into its own script, e.g. elcm_ctl.sql, you could make that:

accept run_it char format a1 prompt "Run the script?"

set termout off

whenever sqlerror exit success rollback
exec if upper(nvl('&run_it', 'N')) != 'Y' then raise no_data_needed; end if;
whenever sqlerror continue

spool c:\elcm.sql
select 'select sysdate from dual;' from dual;
spool off

set termout on

spool c:\elcm_details.spl
@c:\elcm.sql
spool off

The accept command is a bit neater than relying on substitution prompts, in my opinion, and is somewhat self-documenting. This prompts the user with the specified string, rather than just 'gen'. (In SQL*Plus you can extend this and force the user to put in a single character, re-prompting if they put in anything longer; and default to 'N' if they just press return without entering anything. But SQL Developer only supports a subset of the functionality).

Then a small anonymous block throws an exception - doesn't really matter which one - if the entered variable value is not 'y' or 'Y'. While it's doing that I've set termout off so you don't see the actual exception. And I've used whenever sqlerror to make the script exit when that exception is raised, so whatever comes later is not run. That is everything else in the control script, not just the next query, but you could have several sub-scripts if you needed to be more flexible.

But in SQL Developer, termout only works as expected when you run via @. If you run the contents of elcm_ctl.sql directly from the SQL Worksheet you'll see the exception being raised, which is a bit ugly. So instead save that control script and in an empty worksheet just do:

@c:\elcm_ctl.sql

Execute that worksheet as a script and it will prompt you; if you enter 'Y' you'll see the output of the script in the script output window (unless you leave set termout off in the control script), and it will create the spool file. If you enter anything else then it won't run the elcm.sql file, won't show anything in the script output window, and won't create a spool file.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Perfect Alex..I have one more question. I am generating that C:\ELCM.SQL in my script.Can i stop that as well. ? – user3737084 Jun 27 '14 at 09:53
  • You don't need the `decode` if that's what you mean? What I showed is the complete script I was running to verify this. – Alex Poole Jun 27 '14 at 09:56
  • In my script . I generate t C:\ELCM.SQL like this. SPOOL c:\ELCM.SQL select 'select sysdate from dual; from dual; spool off column col noprint new_value elcm_script select decode(lower('&gen'),'y','C:\ELCM.SQL','n', 'null') col from dual; SPOOL c:\ELCM_DETAILS.SPL @&elcm_script spool off I dont want .sql to generate if user types n – user3737084 Jun 27 '14 at 10:00
  • If you generate it after the `exec` check then it will be skipped too, yes. – Alex Poole Jun 27 '14 at 10:10
  • u mean to say whenever sqlerror exit success rollback set termout off exec if upper(nvl('&run_it', 'N')) != 'Y' then "ALL MY .SQL genreation statements raise no_data_needed; end if; set termout on whenever sqlerror continue spool c:\elcm_details.spl @c:\elcm.sql spool off ..Is that correct? – user3737084 Jun 27 '14 at 10:13
  • @user3737084 - not 100% sure I follow, but I've added what I think you mean, creating the script you run from within the control script. – Alex Poole Jun 27 '14 at 10:28