Re: sqlplus substitution variables
- From: "LS Cheng" <exriscer@xxxxxxxxx>
- To: sfaroult@xxxxxxxxxxxx
- Date: Sat, 29 Dec 2007 21:30:30 +0100
yea I finally added dummy parameters so all calls have same number of
parameters then inside the sql script just used decode to null them :-)
Thanks
--
LSC
On Dec 28, 2007 9:42 PM, Stephane Faroult <sfaroult@xxxxxxxxxxxx> wrote:
> 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
> >
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
