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_at_gmail.com> 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_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