How can I make SQL Developer/SQL+ prompt only once for a substitution variable that occurs multiple times in a single statement?
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.
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 (&&)
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.