OraFAQ Forum: Client Tools » Help on passing variables.

Home » Other » Client Tools » Help on passing variables. (Oracle 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help on passing variables. [message #657784]

Tue, 22 November 2016 12:08

Northumbrian
Messages: 3
Registered: November 2016

Junior Member

Hi,

Please don't shoot me if this is very obvious, but I'm not a PL/SQL programmer, and this problem has been driving me mad.

I'm running a batch file on a Windows server, that calls sqlplus with the credentials to run a sql file.... something along the lines of....

sqlplus myid/mypassword@DBNAME @SCRIPTA.SQL
sqlplus myid/mypassword@DBNAME @SCRIPTB.SQL
.
.
.
sqlplus myid/mypassword@DBNAME @SCRIPTZ.SQL

ScriptA-Z are similar in content. All I want to so is to set a couple of variables that will be used in a subordinate sql it then calls.....so

ScriptA.sql
===========
SET ECHO OFF
SET VERIFY OFF
SPOOL SCRIPTA.LOG

VAR1 varchar2(5) := 'TEST1';
VAR2 varchar2(5) := 'TEST2';

begin

VAR1 := 'TEST1';
VAR2 := 'TEST2';
@@SCRIPTAWK.SQL

end;
/
QUIT

======== END of SCRIPTA.SQL

What I need is for the values of Var1 and Var2 to be passed to the subordinate script SCRIPTAWK. That's expecting the two to be populated (they are referenced in the script as &VAR1 and &VAR2. Whenever I run it all I get is to be prompted at runtime for the values of var1, which is not what I wanted. The bottom level scripts are modular so I'd prefer not to hard code everything.

Am I doing something stupid, and can someone point me to where I'm going wrong.

Thanks.

Report message to a moderator

Re: Help on passing variables. [message #657785 is a reply to message #657784]

Tue, 22 November 2016 12:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://docs.oracle.com/database/121/SQPUG/ch_five.htm#SQPUG444

when all else fails Read The fine Manual

https://docs.oracle.com/database/121/SQPUG/toc.htm

Report message to a moderator

Re: Help on passing variables. [message #657789 is a reply to message #657784]

Tue, 22 November 2016 14:47

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Northumbrian

... they are referenced in the script as &VAR1 and &VAR2

What happens if you reference them as &&VAR1 and &&VAR2? (i.e. two ampersands instead of one)

Report message to a moderator

Re: Help on passing variables. [message #657794 is a reply to message #657789]

Wed, 23 November 2016 02:04

Northumbrian
Messages: 3
Registered: November 2016

Junior Member

Thanks both for your replies.

I must admit I'd not heard of the "start" option before. It's going to make life a lot easier. And it worked as well.

I did try the double ampersand option as well, and that didn't work though. Thanks again.

Report message to a moderator

Re: Help on passing variables. [message #657795 is a reply to message #657794]

Wed, 23 November 2016 03:03

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Northumbrian wrote on Wed, 23 November 2016 08:04

and that didn't work though.

It's easier for us to help you if you actually copy and paste your session so that we can see what "it didn't work" means Smile

Report message to a moderator

Re: Help on passing variables. [message #657796 is a reply to message #657795]

Wed, 23 November 2016 03:09

Northumbrian
Messages: 3
Registered: November 2016

Junior Member

Hi,
I've changed the code now to use the "start" option so it's not easy to replicate now. What I did first was to use the scripts "as is" but changed &VAR1 and &VAR2 to &&VAR1 and &&VAR2 and tried. What happened was that I was still being prompted to enter the values of VAR1 and VAR2 at runtime.

I changed the coding to the first suggested resolution using the "start" command supplying the values on that line, and the process worked as expected.

Thanks for your time.

Report message to a moderator

Previous Topic:	Trying to open the XML (CLOB Data) from a table. Getting "out of Memory" error in plsql developer to
Next Topic:	Spooling xls (merged 2)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 00:50:12 CDT 2025