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.

Linseylinseywoolsey

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.

Linseylinseywoolsey

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) variables will use double-ampersand (&&)

Broker

if after you are done and want another value after you commit use this undefine aVariable; – Watchdog

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.

Dionysian

This isn't ideal since the most frequent use case is running the query repeatedly with various values. Secondly, using bind variables as I did will result in better performance since the query can be cached in the shared pool and reused with different values without being parsed anew. – Linseylinseywoolsey

The &&-method is ideal for DDL-scripts – Tilden

How do you force the &&variable to prompt for a new value? – Packet

@Packet You'd either have to close your database session and open a new one, or explicitly undefine the variable. In SQLPlus, the latter would be UNDEFINE variable; off the top of my head, not sure if the same would work in SQL Developer. – Dionysian

Answer 4

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.

Stickler

Answer 5

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.

Flip