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

Question

I have a query something 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 offers me this variable twice, even if it is the same.

If there is a way to make a request only once for a variable that is used twice, how to do it?

I am not using a script, it should be a single executable request.

Answer 1

As I put together 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; 

Then SQL Developer will request a binding variable, you can enter it and click apply.

Answer 2

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

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

Answer 3

I know that you found another way to do this, but the FYI main answer is that if you double the ampersand (for example, use '& & aVariable'), then the value you enter for the replacement variable will be remembered for the duration of your session . Please note that in this case, if you re-execute the request, you will not be asked to repeat the request, it will continue to use the same value.

Answer 4

Therefore, when you use and replace, you simply use a single and faster way instead of entering the value again. Using && the value becomes locked for the variable name. Thus, the && variable would be different from the & variable. Thus, you would be requested once, and the value you entered would be bound to that variable name. Simply put, write your code as follows:

 SELECT * FROM A_TABLE WHERE A_COLUMN = '&&aVariable1' UNION SELECT * FROM A_TABLE WHERE B_COLUMN ='&&aVariable2'; 

Then, if you want to use a different set of values, you will have to change the variable name to something like this: && aVariable3 'and' && aVariable4 'should be sufficient for another set.