Re: sqlplus substitution variables

I opted to add dummy variables

so the call
unload to ache.txt
becomes
unload to ache.txt dummy dummy

then treat second dummy as | in unload.sql (unload default delimiter)

thanks

--
LSC




On Dec 28, 2007 8:57 PM, David Taft <oradbt054@xxxxxxxxx> wrote:

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