Re: A PL/SQL problem...

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: 2000/05/24
Message-ID: <j83ois8uv5k0quv7vark0mkgvls2t067f8@4ax.com>#1/1

On Tue, 23 May 2000 22:21:53 +0200, "cfs.public" <cfs.public_at_wanadoo.fr> wrote:

>I am creating a PL/SQL script that use a variable created with a define
>statement and I need to modify its value within a DECLARE / BEGIN / END;
>statement as follow...

It looks like you are creating a SQL*Plus script. One solution might be to use a SQL*Plus bind variable. For example:

--Declare a user var and a bind var
define vv=5
variable vvbind number;

--set the bind var to the value of the user var --Note the colon in front of the bind variable name. execute :vvbind := &&vv

--Place your pl/sql block here, and modify the bind var. --Note the colon in front of the bind variable name. begin
  :vvbind := 6;
end;
/

--Now you need to get the bind var value back --into your user variable. This is tricky. COLUMN vv_value NEW_VALUE vv
SELECT vvbind vv_value
FROM dual;

--The user variable vv should now contain a 6 DEFINE vv

You can't code an IF...THEN statement outside of a PL/SQL block, but there are some ways to implement conditional logic from within SQL*Plus. My book, SQL*Plus: The Definitive Guide (O'Reilly & Associates, 1999) talks about a whole bunch of this stuff. Since you probably don't have that yet<g>, here's a way to branch to a new script file based on the contents of the vv variable:

COLUMN script_name NEW_VALUE next_file
SELECT DECODE (&vv,5,'file5.sql',6,'file6.sql','bad.sql') FROM dual;

@&next_file

If vv is a 5, then file5.sql will run. If vv is a 6, then file6.sql will run. If vv is neither 5 nor 6, then bad.sql will run.

Write if you have any more questions that I can help you with.

Jonathan

---

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are
>-- Script : TOTO.SQL
>
>define vv = 5;
>
>BEGIN
>-- The following statement is producing an error...
> vv := 6;
>END;
>/
>PROMPT 'VV = ' || &vv;
>
>How can I change the value of vv inside this statement ?
>
>Otherwise, I need to check the value of vv, so I use an IF / THEN / ELSE /
>END IF; statement.
>
>Is there any way to use this statement outside an BEGIN / END; statement in
>a PL/SQL script ?
Received on Wed May 24 2000 - 00:00:00 CDT