Search code examples
oracle-sqldeveloper

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.

Solution

  • 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.