I just tested it, it seems to work, I changed prompt to define 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) > >