| 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.
|
|
|
|
|
|
|
|
|
|
|
|
| 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
