How substitution variables syntax '&1..' works for user/schema delimeter (“.”)
The &1
prompts for a user-entered value. Note how the entered value mytable
is substituted for &1
below:
SQL> SELECT COUNT(*) FROM &1 WHERE col1 = 12;
Enter value for 1: mytable
old 1: SELECT COUNT(*) FROM &1 WHERE col1 = 12
new 1: SELECT COUNT(*) FROM mytable WHERE col1 = 12
COUNT(*)
----------
0
The dot (.
) appends every non-space character that follows the dot to the entered value. Note how the value table
after the dot is appended to the entered my
:
SQL> SELECT COUNT(*) FROM &1.table WHERE COL1 = 12;
Enter value for 1: my
old 1: SELECT COUNT(*) FROM &1.table WHERE COL1 = 12
new 1: SELECT COUNT(*) FROM mytable WHERE COL1 = 12
COUNT(*)
----------
0
The two dots in &1..xxdt
aren't a special operator. The first dot means to append; the second dot is literal. It looks like the &1
in your example is used to prompt for a schema/owner name. Note below how I've entered ed
and &1..mytable
is transformed into ed.mytable
:
SQL> SELECT COUNT(*) FROM &1..mytable WHERE COL1 = 12;
Enter value for 1: ed
old 1: SELECT COUNT(*) FROM &1..mytable WHERE COL1 = 12
new 1: SELECT COUNT(*) FROM ed.mytable WHERE COL1 = 12
COUNT(*)
----------
0
Addendum: Great suggestion from David Aldridge to include a quick explanation of SET DEFINE
, which goes hand-in-hand with variable substitution. Here goes...
The substitutions above are done by SQLPlus, and its behavior can be controlled using SET DEFINE
:
SET DEFINE ON
will allow the substitution and use the defined substitution character. This is normally the SQLPlus default, and was the case when I ran the queries above.SET DEFINE <char>
sets the substitution character. The ampersand (&
) is the usual default. SQLPlus will only accept non-alphanumeric, non-space characters for the substitution character. Note that I've never had to change this value in over a decade of using Oracle.SET DEFINE OFF
will stop substitution. Use this if you need to have an actual literal ampersand in your query or proc, because SQLPlus will treat the ampersand as a substitution character no matter where you put it, including in a string.