Re: sqlplus substitution variables
- From: "LS Cheng" <exriscer@xxxxxxxxx>
- To: william@xxxxxxxxxxxxxxxxxxxx
- Date: Mon, 31 Dec 2007 22:53:48 +0100
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)
>
>
Other related posts:
