I cannot use that approach I think, I need to accept parameter as &1, &2 and so on this is what I did make all script calls with same number of parameters then the the script then looks like set pagesize 0 set termout off set feed off set line 5000 set trimspool on set trimout on set appinfo off set flush off set arraysize 100 set verify off set echo off set wrap off define v_dummy1='&1' define v_spool_file='&2' define v_dummy2='&3' define v_dummy3='&4' define v_sql='&5' col colsep new_value delimiter noprint -- Pipe es el delimitador por defecto de unload de Informix select '"' || decode('&4', 'dummy', '|', '&4') || '"' colsep from dual; set colsep &delimiter spool &v_spool_file @&v_sql spool off Thanks -- LSC On Dec 31, 2007 8:51 PM, William Robertson <william@xxxxxxxxxxxxxxxxxxxx> wrote: > You can make use of the behaviour of COLUMN ... NEW_VALUE for queries > that return no rows, which is to initialise the corresponding variable > without overwriting any existing value. For example, if this is > "test_params.sql": > > > col 1 new_value 1 > col 2 new_value 2 > col 3 new_value 3 > col 4 new_value 4 > col 5 new_value 5 > > set feedback off > > select null "1", null "2", null "3", null "4", null "5" > from dual > where rownum = 0; > > set feedback on > > prompt 1 = &1 > prompt 2 = &2 > prompt 3 = &3 > prompt 4 = &4 > prompt 5 = &5 > prompt > > > then it behaves as follows (tested in SQL*Plus 11g but unchanged for > several versions AFAIK): > > SQL> @test_params > 1 = > 2 = > 3 = > 4 = > 5 = > > SQL> @test_params Mercury > 1 = Mercury > 2 = > 3 = > 4 = > 5 = > > SQL> @test_params Mercury Venus Earth Mars Jupiter Saturn > 1 = Mercury > 2 = Venus > 3 = Earth > 4 = Mars > 5 = Jupiter > > > > > -----Original message----- > From: LS Cheng > Date: 28/12/07 18:27 > > 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) > >