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@xxxxxxxxx> 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
>
>

Other related posts: