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 the functionality 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 PL/SQL block?

Thanks,

~ Justin

Answer 1

I no longer actively use TOAD, but there should be some mechanism for setting values for bind parameters ie select such-and-such from somewhere where ID = :myid; such that every time it occurs TOAD supplies the same value for that parameter.

Alternatively, you could create a session context value or PL/SQL package variable (note: not the same thing as rewriting your entire code to use PL/SQL). See this question

Answer 2

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;