How can I make SQL Developer/SQL+ prompt only once

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

ACCEPT variableToPrompt PROMPT "Enter variable" DEFAULT "test";

SELECT
    *
FROM    
    TABLE
WHERE
    COLUMN = '&variableToPrompt'

Note that the ' in '&variableToPrompt' is only required if your variable is a string. The default value is optional.

Answer 2

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 3

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 4

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 (&&)

Answer 5

So when you use & substitution you are just using the single & as a faster way instead of having to type a value over again. By using the && the value becomes locked-in to the variable name. So &&variable would be different from &&variable1. By doing it this way you will be prompted once and that value you inputted will be adhered to that variable name. Simply put if you write your code like this:

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

Then if you wanted 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.