Re: sqlplus substitution variables

LSC, The only way I know to do this is inside PL/SQL. See the following as an example.

Cheers,

David

• CUT HERE

  ---

  • @test.sql par1,par2,par3 SET FEEDBACK off VERIFY off ECHO off LINESIZE 200 PAGESIZE 0 TRIMSPOOL on SET SERVEROUTPUT on SIZE 1000000 FORMAT WRAP -- DECLARE v_parameters VARCHAR2(2000) := UPPER('&1'); v_par VARCHAR2(32); instrval NUMBER; BEGIN
    • Split string of comma-delimited list of parameters into separate elements. LOOP
      • Get integer location of first comma delimiter found. instrval:= INSTR(v_parameters,',');
        • If no delimters found, then this is the last parameter in the string. IF instrval = 0 THEN v_par:= v_parameters; ELSE
          • Grab string up to next comma delimter found. v_par:= SUBSTR(v_parameters,1,instrval-1);
          • Reset string to everything after current delimiter. v_parameters:= SUBSTR(v_parameters,instrval+1); END IF; --
      • Process each parameter inside the loop. DBMS_OUTPUT.PUT_LINE(v_par); --
      • Exit loop when last parameter has been processed. EXIT WHEN instrval = 0; END LOOP; END; /
• CUT HERE

  ---

On Dec 28, 2007 1:27 PM, LS Cheng <exriscer_at_gmail.com> wrote:

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

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