Conditional Spool with SQL+

There's a way to jerry-rig an if/then using the decode or case statement in SQL+.

The query shown above is the one we want to have a conditional spool option in.

• Line 1 shows the spool file name being defined. It will be used in the query that is called.
• Line 2 calls the query that sets up the spooling (or not)
• Line 3-5 is the actual query.
• Line 6 Will run a query that was generated that is either blank or says 'spool off'

Condtional_Spool.SQL:

--+------------------------------------------------------------------
--| Store all the settings so we don't loose them. To write a file to
--| be executed, the following settings need to be in place.
--+------------------------------------------------------------------
Store Set InitSets Replace
Set Echo Off Verify Off Feedback Off Newpage None Pagesize 0

Accept Spooler Prompt "Spool (N/Y): "

--+------------------------------------------------------------------
--| The New Value will set up an amper variable.  Based on how they
--| answered the above question, set the spool on statement.
--+------------------------------------------------------------------
Column Spool_On New_Value  Spool_On

Spool Spool_On.Sql Replace
Select Case Upper('&Spooler')
            When 'Y' Then 'Spool &Output_File Replace'
                     Else ' '
      End  As  Spool_On
 From Dual;
Spool Off

--+------------------------------------------------------------------
--| Set up the spool off statement based on the user's response.
--| This is called from the calling program.
--+------------------------------------------------------------------
Column Spool_Off New_Value Spool_Off

Spool Spool_Off.Sql Replace
Select Case Upper('&Spooler')
            When 'Y' Then 'Spool Off'
                     Else ' '
       End As   Spool_Off
  From Dual;
Spool Off

--+------------------------------------------------------------------
--| Start the spooling before going back to the calling program
--+------------------------------------------------------------------
@Spool_On

--+------------------------------------------------------------------
--| Clean up: Undefine amper variables and reset settings.
--+------------------------------------------------------------------
Undefine Spooler Spool_On Spool_Off Output_File
@InitSets