Variables in TOAD scripts

Question

I have a SQL script that is being executed in TOAD. Currently, I have it laid out with just statement after statement, thusly:

select such-and-such from somewhere;

delete other-thing from somewhere-else;

And so on. Some of the where clauses end up being repetitive because I have complex inner queries to get particular IDs to operate on. I'd like to capture the ID in the beginning of the script in a variable, and then use that variable in subsequent where clauses. So something like this:

variable MY_ID = select the-ID from somewhere;

select such-and-such from somewhere where ID = @MY_ID;

Obviously, I'm making up that syntax, but that is what I'm looking for. But I'm not sure if that is possible in a TOAD script. I know I can convert the whole thing to a PL/SQL block but I'm trying to avoid having to do that for various reasons.

Any way to do this using TOAD without converting to a PL/SQL block?

Answer 1

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

I use SQL*PLUS substitution variables. They are supported by TOAD. You can execute this code by pressing F5.

COLUMN VAR NEW_VALUE VAR_VALUE

SELECT 'SOMETHING' VAR FROM DUAL;  --this sets 'VAR_VALUE' = 'SOMETHING'

SELECT '&VAR_VALUE' FROM DUAL;  --this uses the value set by the previous stmt.