Home » SQL & PL/SQL » Client Tools » Help on passing variables. (Oracle 11)
Help on passing variables. [message #657784] Tue, 22 November 2016 12:08 Go to next message
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.
Re: Help on passing variables. [message #657785 is a reply to message #657784] Tue, 22 November 2016 12:22 Go to previous messageGo to next message
BlackSwan
Messages: 25745
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
Re: Help on passing variables. [message #657789 is a reply to message #657784] Tue, 22 November 2016 14:47 Go to previous messageGo to next message
Littlefoot
Messages: 21266
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)
Re: Help on passing variables. [message #657794 is a reply to message #657789] Wed, 23 November 2016 02:04 Go to previous messageGo to next message
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.
Re: Help on passing variables. [message #657795 is a reply to message #657794] Wed, 23 November 2016 03:03 Go to previous messageGo to next message
gazzag
Messages: 906
Registered: November 2010
Location: Bristol, 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
Re: Help on passing variables. [message #657796 is a reply to message #657795] Wed, 23 November 2016 03:09 Go to previous message
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.
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:
  


Current Time: Thu Dec 14 04:54:53 CST 2017

Total time taken to generate the page: 0.01673 seconds