Re: sqlplus substitution variables

In message
<6e9345580712281027l4657dd22kd2aa8ca186597428_at_mail.gmail.com>, LS Cheng <exriscer_at_gmail.com> writes
>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

There isn't any direct way, but if you pass missing parameters in as '', they are still processed positionally, but will be empty. nvl should then work.

select nvl('&1','Missing param 1') from dual;
select nvl('&2','Missing param 2') from dual;
select nvl('&3','Missing param 3') from dual;

Try calling the above script with various combinations of parameters

eg @paramtest param1 '' param3

Tested on 11i sqlplus, but I'm fairly sure this behaviour hasn't changed.

-- 
Jim Smith
Ponder Stibbons Limited
251 Barcombe Avenue
London SW2 3BH

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