Re: sqlplus substitution variables

  • From: Jim Smith <jim@xxxxxxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 28 Dec 2007 19:37:40 +0000

In message <6e9345580712281027l4657dd22kd2aa8ca186597428@xxxxxxxxxxxxxx>, LS Cheng <exriscer@xxxxxxxxx> 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

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 12-2007