CODEWITHSUNDEEP
oracle-databasesqlplus
Rob van Laarhoven
Rob van Laarhoven

Reputation: 8915

scope of sqlplus parameters

The following behavior puzzles me. The (positional) parameter of the main script is changed after calling another script.

r1.sql

prompt script r1 : &1

@r2 &1._xxx

prompt script r1 : &1

r2.sql

prompt script r2 : &1

Calling the script:

SQL>sqlplus un/pw @r1 bla

Gives:

script r1 : bla
script r2 : bla_xxx
script r1 : bla_xxx

Is there anyway around this behaviour? Assigning the positional parameters to another variable has the same problem. When the same variable name is used somewhere in the scripts the value is overwritten by a called script. Is there some documentation about the scope of sqlplus variables or how to control the scope?

Upvotes: 2

Views: 937

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

There doesn't seem to be any way to change this behaviour. The only vague references I can find are scattered across the substitution variable documentation, with perhaps the most coherent (but hardly definitive) being in the START docs, though it's pretty similar in @ and @@:

The START command defines the parameters with the values of the arguments; if you START the script again in this session, you can enter new arguments or omit the arguments to use the old values.

The 'omit the arguments' part implies the positional parameters set in the previous call are still available, which means they have to be set in the parent script as well, and can only have overridden any original positional values. It's also a bit misleading - if you START the script again but have run a different script with different parameters in between, you won't get the same old values you might expect from how that's worded; with an r3.sql thrown into the mix and r1.sql amended to:

prompt script r1 : &1
@r2 xxx
prompt script r1 : &1
@r3 yyy
prompt script r1 : &1
@r2
prompt script r1 : &1

... the wording in the docs implies that the second call to r2 would still see xxx, but of course it won't:

script r1 : bla
script r2 : xxx
script r1 : xxx
script r3 : yyy
script r1 : yyy
script r2 : yyy
script r1 : yyy

There isn't any concept of running r2 in the equivalent of a sub-shell. The script your start is just loaded in place, kind of like a file included in a shell script with the . notation. The positional (or if relevant the named) parameters are DEFINEd again, and so there is no scope. Bind variables behave the same way. There is no behavioural difference between calling a separate script with START and just embedding its contents in your main script.

It's also interesting to note that only the passed positional variables are redefined, so if you call your inner script with fewer parameters then some of the originals are preserved; and you don't need to refer to them for the effect to be seen. In fact r2 can be an empty file, and if you have r1 as:

prompt script r1 &1 &2
@r2 xxx
prompt script r1 &1 &2

... and run it as:

sqlplus x/yy @r1 foo bar

... you get:

script r1 foo bar
script r1 xxx bar

None of which helps you very much, but it's too long for a comment...

The only workaround seems to be to define uniquely-named variables in each script that are assigned the positional parameters, but that's a pain and hardly foolproof.

Upvotes: 1

Related Questions