Re: sqlplus substitution variables

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 &3
I 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




--
http://www.freelists.org/webpage/oracle-l

Other related posts: