Oracle SQL*Plus -- Implement Optional Arguments for an SQL*Plus Script

Problem

You need to implement an sqlplus script which accepts an optional parameter(s). 

 

Solution

When invoking a script with arguments, SQL*Plus implicitly defines substitution variables for each argument passed. These substitution variables then can be referenced positionally by &1, &2, and so on for each argument passed.

For any referenced but undefined parameter, SQL*Plus will prompt for a value.

The below script will demonstrate how to prevent any prompting if the script is invoked without any parameters.

 

$ cat script.sql
set termout off
column 1 new_value 1
select '' "1" from dual where 1=2;
set termout on
set heading off
select nvl('&1','param not passed') from dual;
exit 0

$ sqlplus -S -L $CONN @script passing_value
old   1: select nvl('&1','param not passed') from dual
new   1: select nvl('passing_value','param not passed') from dual

passing_value

$ sqlplus -S -L $CONN @script
old   1: select nvl('&1','param not passed') from dual
new   1: select nvl('','param not passed') from dual

param not passed