Oracle SQL*Plus -- Implement Arbitrary Named Parameters for an SQL*Plus Script

 Problem

You want to invoke an SQL*Plus script with arbitrary number of named parameters, mostly like unix/linux style: -p1 v1 -p2 v2 and so on

Solution

The below script might be used as a template for implementing named arguments for an SQL*Plus script. 

 

The script expects to have at most 4 named parameters, this number can be easily extended and named as appropriate. Each passed argument then assigned to a corresponding substitution variable to be used further in the script or for invoking other scripts with:

$ cat start.sql
set verify off

col PARAM1 new_val PARAM1
col PARAM2 new_val PARAM2
col PARAM3 new_val PARAM3
col PARAM4 new_val PARAM4

set termout off
select param1, param2, param3, param4 from (
select * from (
select  decode(instr(val,'-'),1,val) param, lead(val,1) over (order by lvl) value  from (
select regexp_substr(params, '\S+', 1,level) val, level lvl from (
select '&1' params from dual
) connect by level <= regexp_count(params,'\s+') + 1 )
) where param is not null
) pivot (max(value) for param in ('-p1' as param1,'-p2' as param2, '-p3' as param3, '-p4' as param4));
set termout on

prompt PARAM1="&param1"
prompt PARAM2="&param2"
prompt PARAM3="&param3"
prompt PARAM4="&param4"

exit 0

 

Here the script is invoked with all 4 parameters

 

$ sqlplus -S -L  $CONN @start.sql "-p1 val1 -p2 val2 -p3 val3 -p4 val4"
PARAM1="val1"
PARAM2="val2"
PARAM3="val3"
PARAM4="val4"

The script can be invoked with only some of the parameters and in any order:

 

$ sqlplus -S -L  $CONN @start.sql "-p4 value4 -p1 value1"
PARAM1="value1"
PARAM2=""
PARAM3=""
PARAM4="value4"

Note. You can also predefine the substitution variables (PARAM1, PARAM2,...) with default values for cases when the values for the missing arguments not passed