How substitution variables syntax '&1..' works for user/schema delimeter (“.”)

Question

I saw this line in an oracle script

SELECT COUNT(*) INTO version1 FROM &1..xxdt WHERE version = 3310;

I don't understand the &1.. part. I think xxdt is the name of the table, so what is the &1.. thing in front of it?

Answer 1

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.