NVLfunction 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;
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.