Re: sqlplus substitution variables

  • From: "LS Cheng" <exriscer@xxxxxxxxx>
  • To: william@xxxxxxxxxxxxxxxxxxxx
  • Date: Mon, 31 Dec 2007 22:58:27 +0100

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

Other related posts: