Re: sqlplus substitution variables
- From: "David Taft" <oradbt054@xxxxxxxxx>
- To: exriscer@xxxxxxxxx
- Date: Fri, 28 Dec 2007 14:57:26 -0500
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:
