Is there a way in which I don't SPOOL a file based on a criteria? The following code will SPOOL an empty file, which is not useful.
VARIABLE :SPOOLYN AS VARCHAR2(1);
DECLARE
VARSPOOL CHAR ( 1 ) := '&SpoolYN';
BEGIN
:SPOOLYN := VARSPOOL;
END;
** Some sort of IF condition based on variable :SPOOLYN **
SPOOL "File.txt";
-- Including all Data to be SPOOLed and SPOOL OFF;
** End of IF condition **
Since SPOOL is a SQLPlus command, this would be a SQLPlus question, not PL/SQL. Generally it's no big deal, folks just process nothing since most shell scripts are line-based and so no lines does no work. Or they delete the file, or simply skip doing anything further in their shell script.
SQLPlus has no conditional logic of its own. However, it does allow for dynamic replacement and that can be used as a conditional workaround:
spool_data.sql:
spool file.txt
select ...
spool off
donothing.sql: [empty - it's just to avoid getting an error message if you say N]
primary script:
VARIABLE SPOOLYN VARCHAR2(1);
DECLARE
VARSPOOL CHAR ( 1 ) := '&SpoolYN';
BEGIN
:SPOOLYN := VARSPOOL;
END;
/
column x new_value spoolme
select DECODE(:spoolyn,'Y','spool_data','donothing') x from dual;
@&&spoolme
exit
Supply Y at the prompt and it invokes the spool_data.sql script which spools file.txt. Supply N and it invokes the empty donothing.sql script and does nothing. But such an awkward workaround is probably not worth it. An empty file is easier to work with in my opinion. We should just accept that SQLPlus is a command line interface and not a programming language and keep our conditional logic either in PL/SQL or in shell.