Angry_Dragon
Angry_Dragon

Reputation: 21

Oracle SQL Developer - Declare a variable once

I am using Oracle SQL Developer and running my query in a worksheet as a script.

What I wanted to happen is to be prompted to declare the value once.

SELECT * FROM Person WHERE age IN (&age);
SELECT * FROM Job WHERE min_age IN (&age);

However, when I run the query, it prompts me to enter a value for my variable twice.

I have tried to define it at the top. I have tried to use &&age in my second query. I tried to use :age instead.

But each time I have to declare it twice.

Upvotes: 0

Views: 409

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

Use &&age for both references:

SELECT * FROM Person WHERE age IN (&&age);
SELECT * FROM Job WHERE min_age IN (&&age);

If you want to re-run the script and still be prompted (once) on each run then undefine the variable first:

undefine age
SELECT * FROM Person WHERE age IN (&&age);
SELECT * FROM Job WHERE min_age IN (&&age);

From the documentation:

... SQL*Plus automatically DEFINEs any substitution variable preceded by two ampersands, but does not DEFINE those preceded by only one ampersand. When you have defined a variable, SQL*Plus will not prompt for its value in the current session.

If you only modify the second reference as you described in the question:

SELECT * FROM Person WHERE age IN (&age);
SELECT * FROM Job WHERE min_age IN (&&age);

then for the first query you will be prompted but the variable will not be defined; when the second query runs it will define the variable at that point but it has no value yet, so it has to prompt for one. Using &&age in the first query as well defines it then, and the first prompt stores the value, so it doesn't need to prompt for the second one.

Upvotes: 4

Related Questions