Re: sqlplus substitution variables

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

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

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