How can I make SQL Developer/SQL+ prompt only once for a substitution variable that occurs multiple times in a single statement?

Question

I have a query roughly like this:

 select * 
  from A_TABLE 
  where A_COLUMN = '&aVariable'
    union
 select * 
  from A_TABLE 
  where B_COLUMN = '&aVariable';

But when I run it, SQL Developer prompts me for the variable twice, even though it's the same variable.

If there's a way to make it prompt only once for a variable that is used twice, how do I do it?

I do not want to use a script, it must be a single executable query.

Answer 1

As I was forming this post, I figured out how to do it:

 :a_var
 select * 
  from A_TABLE 
  where A_COLUMN = :a_var
    union
 select * 
  from A_TABLE 
  where B_COLUMN = :a_var;

SQL Developer will then prompt for a bind variable, you can enter it and hit apply.

Answer 2

I know you found another way to do it, but FYI the basic answer is that if you double up the ampersand (e.g., use '&&aVariable'), then the value you enter for the substitution variable will be remembered for the length of your session. Note that in this case if you re-execute the query you will not be prompted again, it will keep using the same value.

Answer 3

select * 
  from A_TABLE 
  where A_COLUMN = '&aVariable'
    union
 select * 
  from A_TABLE 
  where B_COLUMN = '&&aVariable';

Notice how the 2nd (and subsequent) variables will use double-ampersand (&&)