I have done a little searching, and not found anything that speaks to this, thought I would ask the experts:
Recently I had to build an SQL script to be run in SQLPlus, and this script invoked another with the @ usage, and passed in a derived value as an argument (would be received as &1 in the 2nd-level code). I wondered if I was facing a misunderstanding with scope, and was hoping you could tell the world for sure.
Test case (no tables or other types involved):
1st-level SQL
set pages 0 feed off head off verify off trims on
variable roll_qtr1 VARCHAR2(6)
exec :roll_qtr1 := to_char(sysdate,'yyyy')||'0'||to_char(sysdate,'q');
col filename1 new_val filename1
SELECT 'test_file_'||:roll_qtr1||'.dat' filename1 FROM dual;
--spool &filename1
@ get_file_data.sql :roll_qtr1
--spool off
2nd-level SQL (@ get_file_data.sql from above)
set pages 0 feed off head off verify on lines 9000 colsep ','
variable parm_qtr varchar2(6)
exec :parm_qtr := '&1';
SELECT :parm_qtr FROM dual;
Now removing the single quotes off the &1 in the 2nd-level SQL gets the value I expect, whereas the code as it is gives:
BEGIN :parm_qtr :=
':roll_qtr1'; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
What gives? It passed the actual bind variable name instead of the value? Which says to me that running an SQL file with "@" is very much the same as, say, an
include command in a C program, or running a KSH file in UNIX using the "." notation for execution - all making whatever happens as if it's all in one process/session. Wouldn't this negate the value of command line parameters within the SQLPlus session? Is there a by-reference vs. by-value thing going on?
I sure would value a chance to learn the right understanding.
When you call a script with:
@script :foo
You're passing the literal value ":foo" to the script. This goes in the substitution variable.
When you access this variable, SQL*Plus replaces it with its current value. It does this as if you had written the statement yourself! So any references to &1 become :foo, as if you had written your script that way in the first place.
So when you access &1 without quotes, it's as if you're using the bind variable :foo. But place it in quotes and you get ':foo', which is the string.
Once you've defined a bind or substitution variable, it's value remains for the rest of your session (or you undefine the substitution)
For example, here's a simple script which assigns the 1st parameter to a bind:
C:\Users\csaxon\Documents>type script.sql
var bar varchar2(10);
var
exec :bar := &1;
exec :bar := '&1';
When you call it, passing :foo, in the first assignment to bar
exec :bar := :foo;
So it's looking for the bind :foo. In the second it becomes:
exec :bar := ':foo';
Which just assigns the string ":foo".
C:\Users\csaxon\Documents>sqlplus chris/chris@db11g
SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 29 11:11:30 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SP2-0158: unknown SET option "sqlformat"
SQL> @script :foo
variable bar
datatype VARCHAR2(10)
SP2-0552: Bind variable "FOO" not declared.
PL/SQL procedure successfully completed.
SQL> var foo varchar2(10);
SQL> exec :foo := 'foo';
PL/SQL procedure successfully completed.
SQL> @script :foo
variable bar
datatype VARCHAR2(10)
variable foo
datatype VARCHAR2(10)
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> print :bar
BAR
--------------------------------
:foo
SQL> define &1
SP2-0553: Illegal variable name ":foo".
SQL> select &1 from dual;
old 1: select &1 from dual
new 1: select :foo from dual
:FOO
--------------------------------
foo
So, long story short. Wherever you have &1 in your script, imagine what the script will do if you use the script parameter value instead. That's the behaviour you'll get.