I think that you have well analyzed the problem ... There is, two my knowledge, two pure SQL*Plus workarounds.
Both rely on always passing the same number of parameters.The simplest one is to pass a specific parameter to mean "no value", for intance '-' :
ORACLE-SQL> !cat test_parameters.sql
select 'parameter1 is ' || case '&1'
when '-' then 'unset'
else '&1'
end || chr(10) ||
'parameter2 is ' || case '&2'
when '-' then 'unset'
else '&2'
end || chr(10) ||
'parameter3 is ' || case '&3'
when '-' then 'unset'
else '&3'
end my_params
from dual
/
ORACLE-SQL> @test_parameters hello happy tax-payer
MY_PARAMS
--------------------------------------------------------------------------------
parameter1 is hello
parameter2 is happy
parameter3 is tax-payer
ORACLE-SQL> @test_parameters hello world -
MY_PARAMS
--------------------------------------------------------------------------------
parameter1 is hello
parameter2 is world
parameter3 is unset
The second one is to pass all the parameters as a single string (by
double-quoting the list) and slicing the list inside your script:
ORACLE-SQL> !cat test_parameters2.sql
select 'Parameter1 is ' || nvl(max(case rn
when 1 then arg
else null
end), 'unset') || chr(10) ||
'Parameter2 is ' || nvl(max(case rn
when 2 then arg
else null
end), 'unset') || chr(10) ||
'Parameter3 is ' || nvl(max(case rn
when 3 then arg
else null
end), 'unset') my_parameters
from (select rn, substr(args, instr(args, ' ', 1, rn) + 1,
instr(args, ' ', 1, rn + 1)
- instr(args, ' ', 1, rn) - 1) arg
from (select ' ' || trim(regexp_replace('&1', ' *', ' ', 1, 0)) || '
' args
from dual),
(select rownum rn
from dual
connect by level <= 3))
/
ORACLE-SQL> @test_parameters2 "hello happy tax-payer"
MY_PARAMETERS
--------------------------------------------------------------------------------
Parameter1 is hello
Parameter2 is happy
Parameter3 is tax-payer
ORACLE-SQL> @test_parameters2 "hello world"
MY_PARAMETERS
--------------------------------------------------------------------------------
Parameter1 is hello
Parameter2 is world
Parameter3 is unset
ORACLE-SQL>
Hope that helps ...
S Faroult
LS Cheng wrote:
Hi Writting some informix migration scripts here and just got a bit stucked. When we invoke sql scripts in sqlplus with arguments such as @test.sql par1 par2 par3 par1, 2 and 3 can be treated as &1, &2 and &3I have a situation where the number of arguments can vary so I can have from par1 only up to par3 inclusive.Basically does anyone know if there is something similar as $# in ksh but for SQLPLUS? Or anyway to treat do something like NVL('&2', ';') (this does not seem to work as it prompts and asks for a value)Thanks -- LSC
-- //www.freelists.org/webpage/oracle-l
