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 > > >