I am using Oracle SQL Developer and running my query in a worksheet as a script.
What I wanted to happen is to be prompted to declare the value once.
SELECT * FROM Person WHERE age IN (&age);
SELECT * FROM Job WHERE min_age IN (&age);
However, when I run the query, it prompts me to enter a value for my variable twice.
I have tried to define it at the top. I have tried to use &&age in my second query. I tried to use :age instead.
But each time I have to declare it twice.
Use &&age for both references:
SELECT * FROM Person WHERE age IN (&&age);
SELECT * FROM Job WHERE min_age IN (&&age);
If you want to re-run the script and still be prompted (once) on each run then undefine the variable first:
undefine age
SELECT * FROM Person WHERE age IN (&&age);
SELECT * FROM Job WHERE min_age IN (&&age);
... SQL*Plus automatically DEFINEs any substitution variable preceded by two ampersands, but does not DEFINE those preceded by only one ampersand. When you have defined a variable, SQL*Plus will not prompt for its value in the current session.
If you only modify the second reference as you described in the question:
SELECT * FROM Person WHERE age IN (&age);
SELECT * FROM Job WHERE min_age IN (&&age);
then for the first query you will be prompted but the variable will not be defined; when the second query runs it will define the variable at that point but it has no value yet, so it has to prompt for one. Using &&age in the first query as well defines it then, and the first prompt stores the value, so it doesn't need to prompt for the second one.
