How to store single result in variable and reuse it in a query (Oracle)?

Question

I have a query like

SELECT id FROM xyz WHERE ...;

which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a script. So my goal was to reuse this result in a variable but I don´t come to a solution which works.

Joining this query would not be a good option in this circumstance. So is it possible to tell Oracle to store this single result in a variable and use this variable to build up new queries - explicitly to use in in the conditional clause of other queries?

If so, perhaps somebody could post a simple example - most simple perhaps would be sth. like

SELECT :VARIABLE FROM DUAL

Answer 1

For Toad, from this answer on SO:

I think this will accomplish what you want. You can declare a bind variable, insert a value into it, and then use it in future statements.

variable l_var varchar2(1);

begin
  select dummy
    into :l_var
    from dual;
end; 

select *
from dual
where dummy = :l_var;

Answer 2

It makes a difference... SQL*Plus can use variables, but they're a SQL*Plus feature and you can just grab that block of code and pass it to an Oracle backend to execute (like you could with a Transact SQL block for example).

You can choose between using bind variables in SQL*Plus as follows: http://www.adp-gmbh.ch/ora/sqlplus/use_vars.html

Or use SQL*Plus's own variables with DEFINE and COLUMN commands: http://www.adp-gmbh.ch/ora/sqlplus/new_value.html

If you really need the code to be executable in other environments, you will probably have to go down the PL/SQL route.