Re: sqlplus substitution variables

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Fri, 28 Dec 2007 21:42:41 +0100
Message-ID: <47755FC1.2050307@roughsea.com>

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

Received on Fri Dec 28 2007 - 14:42:41 CST