Write a SQL script with optional arguments
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;