Write a SQL script with optional arguments

When SQL*Plus parses a script, it replaces the strings &1, &2, etc. with the strings passed as arguments. If only one argument was passed, &2 will be an empty string. You can use the NVL function to use some other value in cases where &2 was omitted: instead of simply using '&2' use NVL ('&2', '&1').

But there's a problem. Substitution variables in SQL*Plus (including &1 and &2) are global in scope: that is, if you call a script named a.sql, then any substitution variables that were defined in a.sql are available in your session even after a.sql ends. In other words, when you say "@res_dt Jan-2005" immediately after starting a SQL*Plus session then &2 will be NULL. But if you say "@res_dt Nov-2004 Feb-2005" followed by "@res_dt Jan-2005", then &2 will be 'Feb-2005'. That was the value it was given when you called res_dt the first time, and the second call does not replace it with NULL (or anything else).

You could use UNDEFINE to make certain the variable &2 is NULL before calling res_dt, but I find it more convenient to always pass two arguments, using something impossible (like =) as a place-holder to indicate a default value. Inside the script, you can use the REPLACE function to convert this place-holder to the default value. Here's the code that results from applying this technique to the res_dt script used in a previous question.

--  res_dt.sql -- Find reservations made in or between
--                two given months.

--  Call this script with two Mon-Year arguments, 
--  like this:

--       @res_dt  Nov-2004  Feb-2005

--  or, instead of a second Mon-Year, you can
--  pass = to do only one month.  That is,
--  the following two calls are equivalent:

--      @res_dt  Jan-2005  Jan-2005
--      @res_dt  Jan-2005  =

--  All reservations made in either month, or
--  in months between them, will be shown.


SELECT   *
FROM     reservation
WHERE    made_dt >= TO_DATE ( '01-&1'
                            , 'DD-Mon-YYYY'
                            )
AND      made_dt <  
           ADD_MONTHS ( TO_DATE ( '01-' || REPLACE ( '&2'
                                                   , '='
                                                   , '&1'
                                                   )
                                , 'DD-Mon-YYYY'
                                )
                      , 1
                      )
ORDER BY made_dt
,        id;