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

sql plsql variables substitution

Trampas Kirk · Apr 9, 2009 · Viewed 26.5k times · Source

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

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.