nested sql scripts and variables

Question

How do I avoid command line parameters being overwritten when i have a nested script that i pass parameters to.

For example:

i run the command sqlplus user/pass @test_include.sql 1 2 3. Which should print the values 1, 2, and 3.

test_include.sql contains a nested sql script that i pass paramters x and y.

The problem is, is that &1, &2 that should be 1 and 2 are being replaced by x and y from the subscript.

Here is the test_include.sql code:

set verify off
set echo on


@ nested_sql_script.sql x y

select
  '&_example_date_' as example_date
from
  dual
;

select '&&1' parm1 from dual;
select '&&2' parm2 from dual;
select '&&3' parm3 from dual;


exit

Here is the code for nested_sql_script.sql:

define _example_date_ = ''


-- store the resulting value in a "replacement" variable named &_CIM_TABLESPACE_NAME_
column my_val new_value _example_date_

select '&1' as x, '&2' as y, to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') as my_val from dual;

select
   '&_example_date_' as nested_sql_example_date
from dual
;

Here is the output:

**sqlplus user/pass @test_include.sql 1 2 3**

 SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 3 15:32:53 2014

 Copyright (c) 1982, 2011, Oracle.  All rights reserved.


 Connected to: Oracle Database 11g Enterprise Edition Release
 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

 SQL> 
 SQL> 
 SQL> @ nested_sql_script.sql x y SQL> define _example_date_ = '' 
 SQL> 
 SQL> 
 SQL> column my_val new_value _example_date_ 
 SQL> 
 SQL> select '&1' as x, '&2' as y, to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') as my_val from dual;

 X Y MY_VAL 
 -------------------
 x y 09/03/2014 15:32:53

 SQL> SQL> select   
 2     '&_example_date_' as nested_sql_example_date
 3  from dual   
 4  ;

 NESTED_SQL_EXAMPLE_DATE
 -------------------
 09/03/2014 15:32:53

 SQL> SQL> SQL> select   
 2    '&_example_date_' as example_date  
 3  from   
 4    dual   
 5  ;

 EXAMPLE_DATE 09/03/2014 15:32:53

 SQL> SQL> select '&&1' parm1 from dual;

 P
 -
 x

 SQL> select '&&2' parm2 from dual;

 P
 -
 y

 SQL> select '&&3' parm3 from dual;

 P
 -
 3

 SQL> SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise
 Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning,
 Automatic Storage Management, OLAP, Data Mining and Real Application
 Testing options

[Related question](http://stackoverflow.com/q/17523488/266304), but doesn't really help, other than mentioning creating uniquely-named variables to hold the original passed values. Essentially, you can't avoid the positional parameters being redefined. — Alex Poole, Sep 03 '14 at 21:23

score 3 · Answer 1 · edited May 23 '17 at 12:08

You can't prevent the positional parameters being replaced; it's a (lack of) scoping issue, really. All you can really do is store the values as named parameters before including the nested script, which is messy:

define orig1=&1
define orig2=&2
define orig3=&3

@nested_sql_script.sql x y

select '&orig1' parm1, '&orig2' parm2, '&orig3' parm3 from dual;

You could assign them to bind variables instead but it's basically the same thing.

nested sql scripts and variables

1 Answers1

Linked