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